What's wrong with declaring a cursor?

-------------------------------------------------------

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER procedure [dbo].[spTableStats] as

/* ---------------------------------------------------------------------------------------------------------

This code, for the current database, lists info about each of its tables:

name, row count, column count, reserved KB, used KB, used percent, category, and description.

Note that the latter 2 are extended properties of each table.

Created by: George Hernandez

Modified: 2001 September 21

*/ ---------------------------------------------------------------------------------------------------------

--Prepare variables and temp table

set nocount on --Don't say how many rows

declare @SQL nvarchar(2000), @TableName sysname, @Columns int, @Rows int

declare @ReservedKB int, @UsedKB int, @UsedPercent real

declare @Category nvarchar(300), @Description nvarchar(300)

if exists(select name from tempdb.dbo.sysobjects where name like '#tbl1%')

drop table #tbl1

create table #tbl1(TableName sysname, Columns int, Rows int,

ReservedKB int, UsedKB int, UsedPercent real, Category nvarchar(300), Description nvarchar(300))

--Fill cursor then populate temp table with columns from extended properties.

declare crsr1 cursor for

select t1.TableName,t1.Columns,t1.Rows,t2.ReservedKB, t2.UsedKB, t2.UsedPercent

from

(select so.name as TableName, count(sc.name) as Columns, si.rowcnt as Rows

from sysobjects as so join syscolumns as sc on (so.id=sc.id)

join sysindexes as si on (so.id=si.id)

where so.type='U' and so.name<>'dtproperties' and si.indid in(0,1)

group by so.name,si.rowcnt) as t1

join

(select so.name as TableName, sum(si.reserved*8) as ReservedKB, sum(si.used*8) as UsedKB

, case sum(si.reserved)

when 0 then 0

else cast(sum(si.used) as real)/cast(sum(si.reserved) as real)*100

end as UsedPercent

from sysobjects as so join sysindexes as si on (so.id=si.id)

where so.type='U' and so.name<>'dtproperties'

group by so.name) as t2 on (t1.TableName=t2.TableName)

--/*

open crsr1

fetch next from crsr1 into @TableName, @Columns, @Rows, @ReservedKB, @UsedKB, @UsedPercent

while @@fetch_status>=0

begin

select @Description=cast(value as nvarchar(300))

from ::fn_listextendedproperty('MS_Description','user','dbo','table',@TableName,null,null)

if @Description is null or @Description='' or @@rowcount=0

select @Description='null'

else

select @Description=quotename(@Description, '''')

select @Category=cast(value as nvarchar(300))

from ::fn_listextendedproperty('Category','user','dbo','table',@TableName,null,null)

if @Category is null or @Category='' or @@rowcount=0

select @Category='null'

else

select @Category=quotename(@Category, '''')

select @SQL=N'insert into #tbl1 '

+'select '

+quotename(@TableName,'''')

+', '+cast(@Columns as nvarchar(50))

+', '+cast(@Rows as nvarchar(50))

+', '+cast(@ReservedKB as nvarchar(50))

+', '+cast(@UsedKB as nvarchar(50))

+', '+cast(@UsedPercent as nvarchar(50))

+', '+@Category

+', '+@Description

--print @SQL

execute(@SQL)

fetch next from crsr1 into @TableName, @Columns, @Rows, @ReservedKB, @UsedKB, @UsedPercent

end

close crsr1

deallocate crsr1

select * from #tbl1 order by TableName

drop table #tbl1

--*/