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 

7 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. Rozana Spokesman is one of the best Punjabi newspaper, cover the news of sports, politics, national news and also cover the international news.

    punjab news in punjabi
    punjab news
    No.1 Punjabi newspaper | Ajit Newspaper | Punjabi Jagran | Jag Bani

    ReplyDelete
  5. For technical support routerlogin.net login Visit our site and get the 24*7 technical support from us.

    ReplyDelete
  6. Монтаж кондиционера включает установку внутреннего и наружного блоков, прокладку трубопроводов для циркуляции хладагента, подключение электропитания и настройку системы. Это требует профессиональных навыков и должно выполняться квалифицированным специалистом для обеспечения правильной установки и оптимальной работы кондиционера. Далее читайте здесь https://stroyklim.com/ustanovka-kondicionera/

    ReplyDelete
  7. Монтаж кондиционера включает установку внутреннего и наружного блоков, прокладку трубопроводов для циркуляции хладагента, подключение электропитания и настройку системы. Это требует профессиональных навыков и должно выполняться квалифицированным специалистом для обеспечения правильной установки и оптимальной работы кондиционера.

    ReplyDelete