SQL query to understand the names of all the avail

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL query to understand the names of all the avail

Author

Topic

prasadmt
Starting Member

1 Post

Posted - 2014-08-31 : 08:58:07

Hi
Please help me with a SQL query to understand the names of all the available tables in the database , number of records in these tables and size of these tables ? Many Thanks

Regards,
Prasad

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-09-02 : 10:27:15

Select the database and new query.

select *
from sys.tables

select count(*)
from sys.tables

SELECT
t.NAME AS TableName,
i.name as indexName,
p.[Rows],
sum(a.total_pages) as TotalPages,
sum(a.used_pages) as UsedPages,
sum(a.data_pages) as DataPages,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%' AND
i.OBJECT_ID > 255 AND
i.index_id <= 1
GROUP BY
t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY
object_name(i.object_id)

We are the creators of our own reality!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources