List Table Names, no.of Columns & Rows

Query to retrieve all the table names with no.of columns and no.of rows

 

Note: This applies from SQL Server 2005 onwards.

Recently, I have started working on a performance issue where customer says the data retrieval operation from the table takes longer time. The table structure is really a shocking one for us as most of the tables are having more than 300 columns which are being used for OLTP system. At this time, we wanted to retrieve all the table names with total no.of columns and no.of rows for each table in a single report. This made me to prepare a small query with basic joins between sysobjects, sys.indexes and syscolumns system views.

Here is the query which has given me the desired output.

select a.name as ObjectName,b.Rows,count(c.object_id) AS [No.Of.Columns] from sysobjects a JOIN sysindexes b ON a.id = b.id
JOIN sys.columns c ON a.id = c.object_id
where a.type = 'U' and b.indid <=1 group by c.object_id,b.rows,a.name order by 3 DESC

Here is a sample report for the above query which I ran against the AdventureWorksDW2012 database.

Sample output of the query