This code lists allt ables without clustered index and the number of rows in them.
set nocount on
create table #Utable (TableName char(100) not null,
Rows integer not null)
Declare @Tableid integer
Declare @Result integer
declare Database_cursor cursor
for select [id] from sysobjects where type ='u' order by name
open Database_cursor
DECLARE @SQLString NVARCHAR(500)
fetch next from Database_cursor into @Tableid
while (@@fetch_status <> -1)
begin
select @Result =count(*) from sysindexes where id = convert(varchar,@Tableid) and indid = 1
if @result =0
begin
insert into #Utable
select object_name(id)as TableName,sum(rows) as Rows from sysindexes where id = @tableid group by id
end
fetch next from Database_cursor into @Tableid
end
close Database_cursor
deallocate Database_cursor
select * from #utable order by rows desc
drop table #Utable
No comments:
Post a Comment