Search

Thursday, July 28, 2011

SQL 2000 code for getting tables without clustered index

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