forum.venkateswarlu.net
      Register      Login
How to get table wise Columns count, Rows count and Size of table.

1 replies to this topic

Jahnavi N #1
Member
164 Points
 Posted on 28 Jul 2015 06:06 AM IST Hi I want query to result columns count, rows count and table size for each table in a database.

Result should like:

Table Name       Row        Columns       Size
-----------------------------------------------------------------------
Customers         5000          15             100KB
Orders               20000         12             250KB
 
SQL Server     765 views     Reply to this topic
Cherukuri Venkateswarlu #2
Member
140 Points
 Replied on 28 Jul 2015 07:41 AM IST SELECT t.Name AS TABLE_NAME, s.Name AS [SCHEMA_NAME], COUNT(c.Name) AS NO_OF_COLUMNS, i.[rows] AS NO_OF_ROWS, SUM(a.used_pages) * 8 AS USED_SPACE_KB FROM sys.tables t INNER JOIN sys.columns c ON t.object_id = c.object_id INNER JOIN sys.sysindexes i ON i.id = OBJECT_ID(t.name) AND i.indid IN (0,1) INNER JOIN sys.partitions p ON t.object_id = p.OBJECT_ID INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id GROUP BY t.Name, s.Name, i.[Rows] ORDER BY t.name 
Reply to this topic