Using system objects we can find tables inflammations like no of row, indexes, key columns, etc.
sys.tables: Returns a row for each user table in SQL Server.
sys.partitions: Contains a row for each partition of all the tables and most types of indexes in the database.
sys.dm_db_partition_stats: Returns page and row-count information for every partition in the current database.
sys.indexes: Contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.
sys.objects: Contains a row for each user-defined, schema-scoped object that is created within a database, including natively compiled scalar user-defined function.
SELECT s.[Name] + N'.' + t.[Name] [Table], p.[Rows] FROM [sys].tables t JOIN [sys].schemas s ON s.SCHEMA_ID = t.SCHEMA_ID JOIN [sys].partitions p ON p.OBJECT_ID = t.OBJECT_ID AND p.index_id IN (0,1); --heap or clustered index
SELECT o.name, ddps.row_count FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id WHERE i.index_id < 2 AND o.is_ms_shipped = 0
References:
