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

--------------------------------------------------------------------------------------------------------