Friday, February 9, 2018

SQL - Query to list tables size columns count and rows count

In our daily professional life, sometimes we get the request to list down all the tables with total columns count, total rows count and occupied size on the disk by that particular table.
SQL Server provides many solutions to get this information. We can use inbuilt stored procedures such as sp_MSForEachTable and sp_SpaceUsed to pull table related information as requested above.
Hence, we are here to use a very simple SQL statement to pull this information as given below-
/* ------
SYS.TABLES returns a row for each user table in SQL Server.
SYS.COLUMNS returns a row for each column of an object that has columns, such as views or tables.
SYS.INDEXES contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.
SYS.PARTITIONS contains a row for each partition of all the tables and most types of indexes in the database.
SYS.ALLOCATION_UNITS contains a row for each allocation unit in the database.
SYS.SCHEMAS contains a row for each database schema.
 */
Use Demo
Go

SELECT
Sch.NAME AS SCHEMA_NAME,
Tab.NAME AS TABLE_NAME,
COUNT(Col.OBJECT_ID) AS COLUMN_COUNT,
Part.ROWS AS ROW_COUNTS,
CAST(ROUND((SUM(Aut.USED_PAGES) / 128.00), 2) AS NUMERIC(36, 2)) AS USED_MB,
CAST(ROUND((SUM(Aut.TOTAL_PAGES) - SUM(Aut.USED_PAGES)) / 128.00, 2) AS NUMERIC(36, 2)) AS UNUSED_MB,
CAST(ROUND((SUM(Aut.TOTAL_PAGES) / 128.00), 2) AS NUMERIC(36, 2)) AS TOTAL_MB

FROM SYS.TABLES Tab
INNER JOIN SYS.COLUMNS Col 
                ON Tab.OBJECT_ID = Col.OBJECT_ID
INNER JOIN SYS.INDEXES Ind
                ON Tab.OBJECT_ID = Ind.OBJECT_ID
INNER JOIN SYS.PARTITIONS Part
                ON Ind.OBJECT_ID = Part.OBJECT_ID AND Ind.INDEX_ID = Part.INDEX_ID
INNER JOIN SYS.ALLOCATION_UNITS Aut
                ON Part.PARTITION_ID = Aut.CONTAINER_ID
INNER JOIN SYS.SCHEMAS Sch
                ON Tab.SCHEMA_ID = Sch.SCHEMA_ID
GROUP BY
                Tab.NAME, Sch.NAME, Part.ROWS
ORDER BY
                Sch.NAME, Tab.NAME
After running the above list, we will get list of all the tables with total columns count, total rows count and occupied size on the disk as given below –
DBName
SchemaName
TableName
ColumnCount
RowCounts
Used_MB
Unused_MB
Total_MB
Demo
dbo
tbl_SalesOrder
133
263
69.62
6.23
75.85
Demo
dbo
tbl_Customers
12565
41724326
30030.71
1305.83
31336.54
Demo
dbo
tbl_SalesPerson
35
1123
18.32
1.64
19.96
Demo
dbo
tbl_Product
12
94
0.28
0.56
0.84

 This query provides results within seconds. If you want to convert size of table in MB, GB or TB then you can do it very easily by modify it according to your requirement. This query is also helpful to provide the information about your data tables also.

No comments:

Post a Comment

Popular Posts