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!
Go to Top of Page
   

- Advertisement -