Thursday, March 15, 2018

Database tables row count with column count

SELECT     
SchemaNM = sch.name
,TableNM = o.name
--,FullTableNM = sch.name+'.'+o.name,
,RowCNT = ddps.row_count
,ColumnCNT = c.ColumnCount
FROM            sys.indexes i
INNER JOIN sys.objects o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id
INNER JOIN sys.schemas sch on o.schema_id = sch.schema_id
INNER JOIN (Select object_id, ColumnCount = count(1) from sys.all_columns group by object_id) c
   on o.object_id = c.object_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
ORDER BY ddps.row_count desc

No comments:

Post a Comment