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