SQL Server Performance

SP to display all tables (rows, space) for a DB.

Discussion in 'Contribute Your SQL Server Scripts' started by rajeev_id, Apr 14, 2004.

  1. rajeev_id New Member

    --This Stored Procedure Displays the number of rows, disk space reserved, and disk space<br />--used by a all tables in the given database with proper order.<br /><br />--Example---------------<br />--exec TableSize 'master'<br />------------------------<br /><br />--Written By Rajeev Srivastava<br />Create Proc TableSize(@DBNm varchar(255))<br />as<br /><br />set nocount on<br />if len(ltrim(rtrim(@DBNm)))=0<br />begin<br />print 'Error<img src='/community/emoticons/emotion-4.gif' alt=':p' />lease Enter Database Name.'<br />--raiserror(50001,-1,-1)<br />return<br />end<br /><br />if (select count(name) from sysdatabases where name=@DBNm)=0<br />begin<br />print 'Error<img src='/community/emoticons/emotion-4.gif' alt=':p' />lease Enter Valid Database Name.'<br />--raiserror(50002,-1,-1)<br />return<br />end<br /><br />DECLARE @TableName varchar(4000)<br /><br />DECLARE @DBNmQuery varchar(255)<br />--set @DBNm='test'<br />set @DBNmQuery= ('SELECT table_name FROM '+ @DBNm + '.information_schema.tables<br />WHERE table_type = ''base table'' and table_schema=''dbo''')<br /><br /><br />exec('DECLARE TableCursor CURSOR FOR ' + @DBNmQuery)<br /><br /><br />OPEN TableCursor<br />Create table #Tbl<br />(<br />Name1 varchar(4000),<br />rows1 varchar(50),<br />reserved1 varchar(50),<br />data1 varchar(50),<br />index_size1 varchar(50),<br />unused1 varchar(50)<br />)<br /><br />FETCH NEXT FROM TableCursor INTO @TableName<br />WHILE @@FETCH_STATUS = 0<br />BEGIN <br />set @TableName=@DBNm +'..'+'sp_spaceused '+ @TableName<br />insert into #Tbl exec(@TableName)<br />print @TableName<br />FETCH NEXT FROM TableCursor INTO @TableName<br />END<br /><br />set nocount off<br />select * from #Tbl order by cast(rows1 as int )desc<br />drop table #Tbl<br />CLOSE TableCursor<br />DEALLOCATE TableCursor<br /><br />
  2. bambola New Member

    Another way to do it is

    pubs..sp_msforeachtable @command1 = 'sp_spaceused ''?'''

    Bambola.

Share This Page