SQL Server -
Script to get Tables name, size and rows
Applicable on 2005/2008 & R2:
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 #temp
EXEC sp_msforeachtable
'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS
col_count,
a.data_size
FROM #temp a
INNER JOIN
information_schema.columns b
ON a.table_name collate database_default
=
b.table_name collate database_default
GROUP BY a.table_name, a.row_count,
a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer)
DESC
DROP TABLE #temp
Brgds,
Chhavinath Mishra
Database Administrator
Microsoft Certified IT Professional
(MCITP)
SQL Server - Script to get Tables name, size and rows
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 #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp
Database Administrator
Microsoft Certified IT Professional
(MCITP)
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 #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp
Brgds,
Chhavinath Mishra
No comments:
Post a Comment