RSS

Tables Statistics – MS SQL Server

12 Dec

images

You can use the following T-SQL script to get more info and statistics about your database tables:

SELECT
 T.Name AS 'Table Name',
 T.create_date AS 'Creation Date',
 S.Name AS 'Schema Name',
 P.Rows AS 'Rows Count',
 SUM(A.total_pages) * 8 AS 'Total Space KB', 
 SUM(A.used_pages) * 8 AS 'Used Space KB', 
 (SUM(A.total_pages) - SUM(A.used_pages)) * 8 AS 'Unused Space KB'
FROM
 sys.tables AS [T]
 INNER JOIN sys.indexes AS [I]
 ON T.OBJECT_ID = I.object_id
 INNER JOIN sys.partitions AS [P] 
 ON I.object_id = P.OBJECT_ID AND I.index_id = P.index_id
 INNER JOIN sys.allocation_units AS [A] 
 ON P.partition_id = A.container_id
 LEFT OUTER JOIN sys.schemas AS [S] 
 ON T.schema_id = S.schema_id
 WHERE 
 T.NAME NOT LIKE 'dt%' 
 AND T.is_ms_shipped = 0
 AND I.OBJECT_ID > 255 
 GROUP BY 
 T.Name, T.create_date, S.Name, P.Rows
 ORDER BY 3 DESC
 
Leave a comment

Posted by on December 12, 2013 in MS SQL Server

 

Tags: , , , , , , , , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: