Exploring SQL Server System Objects for Table Insights
In SQL Server, system objects can provide valuable information about database tables, such as the number of rows, indexes, key columns, and more. Below are some important system views that can help you retrieve such details.
sys.tables: This system view returns a row for each user-defined table within the database.sys.partitions: Contains a row for each partition associated with all tables and most index types in the database.sys.dm_db_partition_stats: Provides the page and row count information for each partition in the current database.sys.indexes: This view holds a row for each index or heap of a table, view, or table-valued function.sys.objects: This contains a row for every user-defined, schema-scoped object created within the database. It includes natively compiled scalar user-defined functions.
Sample Queries
You can use the following SQL queries to retrieve useful information about your database tables and indexes:
Query to Get Table Names and Row Counts
SELECT s.[Name] + N'.' + t.[Name] AS [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 indexQuery to Get Indexes and Row Counts
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;
