Register

DDL Query to Provide Statistics about All Tables Rows Count

Tools and Materials needed by SQL Server Admins worldwide.
Site Admin
Posts: 148
Joined: 27 Feb 2012, 16:26
Location: Egypt , Cairo

DDL Query to Provide Statistics about All Tables Rows Count

Postby Softex » 09 Feb 2016, 10:59

In order to Quickly Compare 2 Databases after Transfering Specific or all Tables, the best way to check for Rows Count after the transfer.

The Below Query provides Very Good Result from any Database, Containing listing of all Tables + Primary Index + Rows Count + Data on Size and Data Pages Count

If you are suspecting that your database is going to corruption, this script can help you identify whether you have a problem or not, usually Corrupted or Damaged Tables cannot retrieve Row Count on itself.

Code: Select all

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)



Result of Script as Following , Super Great. Yea :)


SQL Table Statistics Code Result Example.png
Softex Software House
Tech. Team
http://www.softexsw.com

Return to Microsoft SQL Server Database Systems

Who is online

Users browsing this forum: No registered users and 1 guest