use master
go
set quoted_identifier on
go
if object_id('sp_rpttblspc') is not null
        drop procedure sp_rpttblspc 
go
create procedure sp_rpttblspc (@dbname varchar(30) = null, @tblname varchar(31) = null)
as
/********************************************************************************/
/*      Created By :    Umachandar Jayachandran (UC)                            */
/*      Created On :    15 April 1996                                           */
/*      Description:    Used to create a report of tables & number of rows.     */
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                 */
/********************************************************************************/
set nocount on
declare @tablename varchar(30), @cmdstr varchar(255)
select @dbname = isnull(@dbname, db_name()), @tblname = isnull(@tblname , '') + '%'
if db_id(@dbname) is null
        begin
                raiserror('Invalid database name was specified.', -1, -1)
                return(1)
        end
create table #tblspc
(name varchar(30), rows varchar(10), reserved varchar(20), data  varchar(20),
 index_size varchar(20), unused varchar(20))
exec('declare tbls cursor for select name from "' + @dbname + 
        '"..sysobjects where type = ''U'' and name like ''' + @tblname + '''')
open tbls
while('FETCH IS OK' = 'FETCH IS OK')
begin
        fetch next from tbls into @tablename
        if @@fetch_status < 0 break
        select @cmdstr = 'use "' + @dbname + '" exec sp_spaceused ''' + @tablename + ''''
        insert into #tblspc exec(@cmdstr)
        if @@error <> 0
                begin
                        deallocate tbls
                        raiserror('Fatal error, unable to obtain space details for tables.', -1, -1)
                        return(1)
                end
end
deallocate tbls
select name as "Table Name:", rows as "Number Of Rows:" ,
        data as "Data Size", index_size as "Index Size",
        reserved as "Allocated Size"
from #tblspc
order by convert(int, rows) desc, 1
return(0)
go

This page was last updated on May 01, 2006 04:28 PM.