SQL SERVER – Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database
USE DatabaseName
GO
CREATE TABLE #temp (table_name sysname ,row_count INT,reserved_size VARCHAR(50),data_size VARCHAR(50),index_size VARCHAR(50),unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #tempEXEC sp_msforeachtable 'sp_spaceused ''?'''SELECT a.table_name,a.row_count,COUNT(*) AS col_count,a.data_sizeFROM #temp aINNER JOIN information_schema.columns bON a.table_name collate database_default= b.table_name collate database_defaultGROUP BY a.table_name, a.row_count, a.data_sizeORDER BY a.row_count desc,
CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp
GO
CREATE TABLE #temp (table_name sysname ,row_count INT,reserved_size VARCHAR(50),data_size VARCHAR(50),index_size VARCHAR(50),unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #tempEXEC sp_msforeachtable 'sp_spaceused ''?'''SELECT a.table_name,a.row_count,COUNT(*) AS col_count,a.data_sizeFROM #temp aINNER JOIN information_schema.columns bON a.table_name collate database_default= b.table_name collate database_defaultGROUP BY a.table_name, a.row_count, a.data_sizeORDER BY a.row_count desc,
CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp
Comments