Following Script will help us to find space utilized by every database object, this includes number of rows and size taken by every table.
DECLARE @projected_size nvarchar(2)
SET @projected_size = NULL
declare @db_count bigint,
@loop_count bigint,
@sqlstat varchar(7000),
@sqlstat2 varchar(7000),
@sqlstat3 varchar(7000),
@sqlstat4 varchar(7000),
@sqlstat5 varchar(7000),
@db_name varchar(150)
CREATE TABLE #tablestats
(
database_name varchar(150),
table_name varchar(700),
table_id bigint,
data_size nvarchar(38),
reserved_size nvarchar(38),
unused_size nvarchar(38),
index_size nvarchar(38),
primary_key_size nvarchar(38),
number_rows_table bigint
)
CREATE TABLE #alltablestats
(
database_name varchar(150),
table_name varchar(700),
table_id bigint,
data_size nvarchar(15),
reserved_size nvarchar(15),
unused_size nvarchar(15),
index_size nvarchar(15),
primary_key_size nvarchar(15),
number_rows_table bigint
)
CREATE TABLE #tabpage
(
database_name varchar(150),
table_name varchar(700),
table_id bigint,
page_size dec(30,0),
used_size dec(30,0),
index_size dec(30,0)
)
CREATE TABLE #pkhold
(
pk_id bigint,
pk_name varchar(400),
parent_id bigint,
pk_size nvarchar(15)
)
CREATE TABLE #capacity
(
tb_id bigint identity,
database_name varchar(150),
database_id bigint
)
IF @projected_size IS null or @projected_size = ‘0’
SET @projected_size = ‘1’
SET @loop_count = 1
/*****************************************************************************
***get database names on server ***
******************************************************************************/
INSERT INTO #capacity(database_name,database_id)
SELECT DISTINCT name,dbid
FROM master..sysdatabases
SELECT @db_count = count(*)
FROM #capacity
WHILE @loop_count <= @db_count
BEGIN
SELECT @db_name = database_name
FROM #capacity
WHERE tb_id = @loop_count
/*****************************************************************************
***get table name,id, and data size ***
******************************************************************************/
SET @sqlstat = ‘use ‘ + @db_name
SET @sqlstat2 = ‘ insert into #tablestats(database_name,table_name,table_id)
SELECT ”’+ @db_name + ”’,name, ID FROM SYSOBJECTS WHERE XTYPE =”U”’
EXEC (@sqlstat + @sqlstat2)
SET @sqlstat2 = ‘ insert into #tabpage(database_name,table_name,table_id,page_size,used_size)
select distinct database_name,table_name, table_id, ”page size” = (SELECT isnull(SUM(DPAGES),0)
FROM SYSINDEXES
WHERE SYSINDEXES.INDID < 2 AND
SYSINDEXES.ID = #tablestats.table_id),’
SET @sqlstat3 = ”’used size” = (SELECT ISNULL(SUM(USED),0)
FROM SYSINDEXES
WHERE SYSINDEXES.INDID = 255 AND
SYSINDEXES.ID = #tablestats.table_id)
from #tablestats ‘
EXEC (@sqlstat + @sqlstat2+@sqlstat3)
SET @sqlstat2 = ‘ update #tablestats set data_size = (select LTRIM(STR((page_size + used_size ) ‘
SET @sqlstat3 = ‘* 8192 / 1024.*’+@projected_size+’,15,0) + ” ” + ”KB”) from #tabpage ‘
SET @sqlstat4 = ‘where #tabpage.table_id = #tablestats.table_id and #tabpage.table_name = #tablestats.table_name) from #tablestats ‘
EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get reserved size ***
******************************************************************************/
SET @sqlstat2 = ‘ update #tablestats set reserved_size = (SELECT LTRIM(STR(cast(SUM(RESERVED) as bigint) ‘
SET @sqlstat3 = ‘ * 8192 / 1024.*’+@projected_size+’,300,0) + ” ” + ”KB”) FROM SYSINDEXES ‘
SET @sqlstat4 = ‘ WHERE SYSINDEXES.INDID IN (0,1,255) AND SYSINDEXES.ID = #tablestats.table_id) from #tablestats ‘
EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get unused size ***
******************************************************************************/
SET @sqlstat2 = ‘ update #tablestats set unused_size = (SELECT LTRIM(STR((cast(SUM(RESERVED) as bigint)- SUM(USED)) ‘
SET @sqlstat3 = ‘ * 8192 / 1024.*’+@projected_size+’,15,0) + ” ” + ”KB”) FROM SYSINDEXES ‘
SET @sqlstat4 = ‘ WHERE SYSINDEXES.INDID IN (0,1,255) AND SYSINDEXES.ID = #tablestats.table_id) from #tablestats ‘
EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get index size ***
******************************************************************************/
DELETE
FROM #tabpage
SET @sqlstat2 = ‘ insert into #tabpage(table_id,page_size,used_size,index_size)select table_id, ‘
SET @sqlstat3 = ‘ ”page size” = (SELECT SUM(DPAGES) FROM SYSINDEXES WHERE SYSINDEXES.INDID < 2 AND SYSINDEXES.ID = #tablestats.table_id), ‘
SET @sqlstat4 = ‘ ”used size” = (SELECT SUM(convert(dec(15),USED)) FROM SYSINDEXES WHERE SYSINDEXES.INDID in (255) AND SYSINDEXES.ID = #tablestats.table_id), ‘
SET @sqlstat5 = ‘ ”index_size” = (SELECT SUM(convert(dec(15),USED))FROM SYSINDEXES WHERE SYSINDEXES.INDID in (0,1,255) AND SYSINDEXES.ID = #tablestats.table_id) from #tablestats ‘
EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4+@sqlstat5)
SET @sqlstat2 = ‘ update #tablestats set INDEX_size = (SELECT LTRIM(STR((index_SIZE – (PAGE_SIZE + ISNULL(USED_SIZE,0))) ‘
SET @sqlstat3 = ‘ * 8192 / 1024.*’+@projected_size+’,15,0) + ” ” + ”KB”) FROM #TABPAGE ‘
SET @sqlstat4 = ‘WHERE #tabPAGE.table_id = #tablestats.table_id) from #tablestats’
EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get PK size ***
******************************************************************************/
SET @sqlstat2 = ‘ INSERT INTO #PKHOLD(PK_ID,PK_NAME,PARENT_ID) SELECT SO.ID, ‘
SET @sqlstat3 = ‘ SO.NAME,SO.PARENT_OBJ FROM SYSOBJECTS SO INNER JOIN #tablestats TS ON ‘
SET @sqlstat4 = ‘ SO.PARENT_OBJ = TS.TABLE_ID WHERE SO.XTYPE = ”PK”’
EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
UPDATE #tablestats
SET primary_key_size = index_size
FROM #PKHOLD pk INNER JOIN #tablestats tb
ON pk.PARENT_ID = tb.table_id
/*****************************************************************************
***get number rows ***
******************************************************************************/
SET @sqlstat2 = ‘ update #tablestats set number_rows_table = rows*’+@projected_size
SET @sqlstat3 = ‘ from sysindexes si inner join #tablestats tb on si.id = tb.table_id ‘
SET @sqlstat4 = ‘ where si.indid < 2 ‘
EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
INSERT INTO #alltablestats(database_name,table_name,table_id,data_size,
reserved_size,unused_size,index_size,
primary_key_size,number_rows_table)
SELECT database_name,table_name,table_id,data_size,
reserved_size,unused_size,index_size,
primary_key_size,number_rows_table
FROM #tablestats
ORDER BY database_name
DELETE FROM #tablestats
DELETE FROM #tabpage
DELETE FROM #pkhold
SET @loop_count = @loop_count + 1
END
/*****************************************************************************
***return results to the screen ***
******************************************************************************/
SELECT database_name,table_name,data_size,
reserved_size,unused_size,index_size,
primary_key_size,number_rows_table
FROM #alltablestats
ORDER BY DATABASE_NAME,TABLE_NAME
DB Cloud TECH 