Read All Tables from SQL with respect of table rows or table size

Dear All,
If you need to read how many tables of SQL in your database has major record this script will help you.


SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    ist.TABLE_CATALOG as tableCatalog,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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
INNER JOIN
  INFORMATION_SCHEMA.TABLES ist on ist.TABLE_NAME = t.name

LEFT OUTER JOIN
    sys.schemas 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, s.Name, p.Rows, ist.TABLE_CATALOG
ORDER BY
    p.rows desc


I hope it will work for your

Regards
Rashid imran Bilgrami