/ Published in: SQL
SQL Server - everything you want to know (or not) about a table.
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
SELECT top 10 @@ServerName AS ServerName, db_name() AS [DATABASE], object_schema_name(t.object_id) AS [schema], --object_name(t.object_id) as [name], (SELECT COUNT(1) FROM sys.columns AS c WHERE c.object_id = t.object_id) AS [COLUMNS], (SELECT name FROM sys.database_principals WHERE principal_id = isnull(t.principal_id,(SELECT principal_id FROM sys.schemas AS s WHERE s.schema_id = t.schema_id)) ) AS [owner], t.*, s.* FROM sys.tables AS t JOIN (SELECT object_id, --count(partition_id) COUNT(DISTINCT index_id) AS indexes, MAX(CASE WHEN index_id = 0 THEN 'Heap' WHEN index_id = 1 THEN 'Clustered' ELSE '' END) AS HOBT, MAX(partition_number) AS partitions, SUM(in_row_data_page_count)*8192 AS in_row_data_page_bytes, SUM(in_row_used_page_count)*8192 AS in_row_used_page_bytes, SUM(in_row_reserved_page_count)*8192 AS in_row_reserved_page_bytes, SUM(lob_used_page_count)*8192 AS lob_used_page_bytes, SUM(lob_reserved_page_count)*8192 AS lob_reserved_page_bytes, SUM(row_overflow_used_page_count)*8192 AS row_overflow_used_page_bytes, SUM(row_overflow_reserved_page_count)*8192 AS row_overflow_reserved_page_bytes, SUM(used_page_count)*8192 AS used_page_bytes, SUM(reserved_page_count)*8192 AS reserved_page_bytes, SUM(CASE WHEN index_id < 2 THEN ROW_COUNT ELSE 0 END) AS ROWS FROM sys.dm_db_partition_stats AS x GROUP BY object_id ) AS s ON s.object_id = t.object_id ORDER BY used_page_bytes DESC, [schema],[name]