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 

6 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
  3. Writing Invaders Provide custom essay writing help,
    dissertation writing services
    Assignments, Essay Writing
    Thesis Writing Services
    Need to pass the Semester…. But having a big headache of completing Essays and Assignments as per deadlines….Don’t fret, we have a quick and complete solution for your problem…. Our PhD holder Writers will help you write your essays and assignments and get an excellent grade….
    Our Academic Writing services include:
    ü Help on Essays / Assignments
    ü Projects / Case Studies
    ü 100% Plagiarism Free Content
    ü Consultancy in writing Thesis
    ü Guidance for writing Dissertation
    ü 100% Genuine Content
    ü Research work
    ü Satisfaction guaranteed
    ü Data Analysis and Interpretation
    ü Technical Writing
    ü Proofreading and Editing
    ü Power Point Presentations
    ü Academic Website Creation for IT Students
    ü Help on AdmissioDissertationn Essays
    Unique Features:
    Best Rates in the Industry
    Email: info@writinginvaders.com
    contact:+17325647555
    whatsaap:+17325647555





    ReplyDelete
  4. This is really great work. Thanks for sharing blog; this is really interesting information for me. We have already set a high standard for assignment help Australia.

    ReplyDelete
  5. Thanks for sharing your wealthy information. This is one of the excellent posts which I have seen. I go through your all of your blog, but this blog is the best one. It is really what I wanted to see hope in future you will continue for sharing such an excellent post. We also provide blog CDR Report.

    ReplyDelete