:::: MENU ::::

Row count for all tables with clustered columnstore index in a database

Columnstore Indexes, T-SQL Scripts, T-SQL Tips and Tricks

Row count for all tables with clustered columnstore index in a database

Consider a scenario that you require to get number of rows for tables in a database with clustered column store index (CCI). It would require to access three tables to get the required info. Following script will get the row count for all tables with CCI

-- Get row count for all tables with CCI in a DB
SELECT schema_name(o.schema_id)+'.'+o.name AS CCITables,
sum(si.rowcnt) AS NoOfRows
FROM sys.objects o
INNER JOIN sysindexes si ON o.object_id = si.id
INNER JOIN sys.indexes i ON o.object_id = i.object_id
AND i.type = 5
GROUP BY schema_name(o.schema_id)+'.'+o.name
ORDER BY NoOfRows DESC
GO

Consult us to explore the Databases. Contact us