Search

Thursday, July 28, 2011

Code to get tables without any Clustered indexes and the number of rows in the tables

This code is for sql 2005:

create table #Ttable (Table_name varchar(100) collate latin1_general_CI_AS not null)
insert into #Ttable
SELECT t.name AS table_name
FROM sys.tables AS t
WHERE NOT EXISTS
(
SELECT * FROM sys.indexes AS i
WHERE i.object_id = t.object_id
AND i.type = 1 -- or type_desc = 'CLUSTERED'
)
ORDER BY table_name;
select Table_name,i.rows
from sysindexes i, sysobjects o, #Ttable t
where i.id = o.id and t.table_name = o.name collate latin1_general_CI_AS
and i.indid <= 1
and o.type = 'U'
order by i.rows desc
drop table #Ttable

No comments:

Post a Comment