Systems Engineering and RDBMS

Finding tables without any indexes

Posted by decipherinfosys on March 14, 2007

Here is a simple SQL Server script that you can use to find out the tables that do not have any indexes defined on them and get the record count for those:

select s.name Table_Name, i.rows
from sysobjects as s
inner join sysindexes i
on s.id = i.id
where s.xtype = ‘u’
and objectproperty(s.id, ‘TableHasIndex’ ) = 0
and i.indid = 0

Needless to state, your actual production tables should all be properly designed, should have constraints on them and proper indexes. This script helps to point out any such anomalies that might exist in the schema.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: