Sunday, April 26, 2020

How to get the Total No.of Columns, Rows and Size of each Table from a Database in SQL Server

T-SQL Query to get the Total No.of Columns, Rows and Size of each Table from a Database in SQL Server
Scenario:
We wants to list all the Tables from a Database with meta data information like Total No.of Columns, Rows, the Primary Key Constraint and Column Name. We can get these details using the below query :

USE AnalyticsDB
GO

SELECT Schema_Name,Table_ID,Table_Name,PK_Constraint, C2.Name AS PK_Column, 
             Total_Cols, Total_Rows, Total_Space_KB, Used_Space_KB, UnUsed_Space_KB
FROM
    (
SELECT S.Name AS Schema_Name,
T.Object_ID AS Table_ID,
T.Name AS Table_Name,
I.Name AS PK_Constraint,
COUNT(C.Name) AS Total_Cols,
P.Rows AS Total_Rows,
CONCAT(SUM(A.Total_Pages)*8 ,' KB') AS Total_Space_KB,
CAST(ROUND(((SUM(A.Total_Pages)*8)/1024.00), 2) AS NUMERIC(36,2)) AS Total_Space_MB,
CONCAT(SUM(A.Used_Pages)*8,' KB') AS Used_Space_KB,
CAST(ROUND((SUM(A.Used_Pages)*8)/1024.00, 2) AS NUMERIC(36,2)) AS Used_Space_MB,
(SUM(A.Total_Pages)-SUM(A.Used_Pages))*8 AS UnUsed_Space_KB,
CAST( ROUND((((SUM(A.Total_Pages)-SUM(A.Used_Pages)) *8 )/1024.00), 2) AS NUMERIC(36,2)) AS UnUsed_Space_MB
FROM  Sys.Tables T
INNER JOIN
Sys.Columns C ON T.Object_ID=C.Object_ID
INNER JOIN
Sys.Indexes I ON T.Object_ID=I.Object_ID
INNER JOIN
Sys.Partitions P ON  I.Object_ID=P.Object_ID AND I.Index_ID=P.Index_ID
INNER JOIN 
Sys.Allocation_Units A ON P.Partition_ID=A.Container_ID
LEFT JOIN
Sys.Schemas S ON T.Schema_ID=S.Schema_ID
WHERE T.Type='U' AND T.Name <> 'sysdiagrams'
GROUP BY S.Name, T.Object_ID,T.Name, I.Name,P.Rows
) S1
LEFT JOIN
Sys.Indexes I2
ON S1.Table_ID= I2.Object_ID AND S1.PK_Constraint=I2.Name
LEFT JOIN Sys.Index_Columns IC2
ON IC2.Object_ID = I2.Object_ID   AND  IC2.Index_ID = I2.Index_ID
LEFT JOIN Sys.Columns C2
ON I2.Object_ID = C2.Object_ID  AND C2.Column_ID = IC2.Column_ID

Result:

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog