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