Sunday 9 September 2012

Table usage script for SQL Server Database

set nocount on

DECLARE @tablename VARCHAR(250)

DECLARE db_cursor CURSOR FOR 
select table_schema+'.'+table_name
from information_schema.tables
where table_type='BASE TABLE'

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @tablename  

WHILE @@FETCH_STATUS = 0  

BEGIN  

          DECLARE @sqltableusage
          TABLE(name varchar(250),rows int,reserved varchar(20),
          data varchar(20), index_used varchar(20), unused varchar(20))
          insert into @sqltableusage
          Exec sp_spaceused @tablename 

       FETCH NEXT FROM db_cursor INTO @tablename  

END  
CLOSE db_cursor  
DEALLOCATE db_cursor

--select * from @sqltableusage

select name,rows,replace(reserved,'KB','') Reserved_KB,replace(data,'KB','') Data_KB,
replace(index_used,'KB','') Index_Used_KB,replace(unused,'KB','') Unused_KB,
convert(decimal(14,2),(replace(reserved,'KB','')))/1024 as Reserved_MB,
convert(decimal(14,2),(replace(data,'KB','')))/1024 as Data_MB,
convert(decimal(14,2),(replace(index_used,'KB','')))/1024 as Index_Used_MB,
convert(decimal(14,2),(replace(unused,'KB','')))/1024 as Unused_MB
from @sqltableusage