Select number of Row, Columns, Size of data for each table in the database

Ranjeet_8
Posted by Ranjeet_8 under Sql Server category on | Points: 40 | Views : 1226

USE Your_DataBase_Name
GO
CREATE TABLE #Temp_tbl (
Table_Name sysname,
Number_of_Row INT,
Temp_Para3 VARCHAR(15),
Size_Of_Data VARCHAR(15),
Temp_Para5 VARCHAR(15),
Temp_Para6 VARCHAR(15))
SET NOCOUNT ON
INSERT #Temp_tbl
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT t1.Table_Name,t1.Number_of_Row,
COUNT(*) AS Number_Of_Column,t1.Size_Of_Data
FROM #Temp_tbl t1
INNER JOIN INFORMATION_SCHEMA.COLUMNS t2
ON t1.Table_Name collate database_default
= t2.Table_Name collate database_default
GROUP BY t1.Table_Name, t1.Number_of_Row, t1.Size_Of_Data
ORDER BY CAST(REPLACE(t1.Size_Of_Data, ' KB', '') AS integer) DESC

DROP TABLE #Temp_tbl

Comments or Responses

Login to post response