Search

Monday, June 2, 2014

Find Table with Max. rows & Size

You can use the below query to get all tables with No of rows and size. User required View Database State permission on the Server to use below DMV

SELECT SN.Name AS [Schema Name], ST.Name AS [Table Name], SI.Name AS [Index Name], PS.Reserved_Page_Count * 8 AS [Total Space Consumed (in KB)], PS.Used_Page_Count * 8 AS [Used Space (in KB)], (PS.Reserved_Page_Count - PS.Used_Page_Count) * 8 AS [Free Space (in KC)], CASE WHEN PS.Index_ID IN (0, 1) THEN PS.Row_Count ELSE NULL END AS [Row Count]
FROM SYS.DM_DB_PARTITION_STATS AS PS INNER JOIN SYS.OBJECTS AS ST ON ST.OBJECT_ID = PS.OBJECT_ID INNER JOIN SYS.SCHEMAS AS SN ON SN.SCHEMA_ID = ST.SCHEMA_ID LEFT JOIN SYS.INDEXES AS SI ON SI.OBJECT_ID = PS.OBJECT_ID AND SI.INDEX_ID = PS.INDEX_ID
WHERE ST.IS_MS_SHIPPED = 0
ORDER BY [Total Space Consumed (in KB)] DESC 

2 comments:

  1. Hi,

    Why do we need to check for the space in this query?

    Thanks
    Susan

    ReplyDelete
  2. This is very much a work in progress whenever I customwritings.com review find out about one who is more beautiful than any of these I will add her and kick out number ten Thanks for sharing the informative post.

    ReplyDelete