Finding tables w/o indexes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Finding tables w/o indexes

Author

Topic

billsox
Yak Posting Veteran

74 Posts

Posted - 2005-03-01 : 10:51:03

Hi -- How can I get a list of all of the tables in a database that don't have any indexes? Is it as simple as outer joining the sysobjects table to the sysindexes table where the id on sysindexes is Null? Thanks in advance...

Bill

PW
Yak Posting Veteran

95 Posts

Posted - 2005-03-01 : 11:05:07

You need to check 'indid' in sysindexes, since there is an entry with indid zero for the table itself.

Select name
from sysobjects as o
where type = 'U'
and not exists (
Select *
from sysindexes as i
where o.id = i.id
and i.indid > 0
)

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-01 : 11:05:43

[code]
SELECT t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
ON t.TABLE_CATALOG = c.TABLE_CATALOG
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
AND c.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE c.TABLE_NAME IS NULL

[/code]

Brett

8-)

PW
Yak Posting Veteran

95 Posts

Posted - 2005-03-01 : 11:08:40

What about indexes that aren't the primary key ?

Why a LEFT JOIN with a WHERE clause, when SQL provides the EXISTS operator specifically to handle correlation and checking for existance/non-existance between 2 related sets ?

billsox
Yak Posting Veteran

74 Posts

Posted - 2005-03-01 : 11:16:40

PW has a point. I need a list of the tables that don't contain any indexes, not just primary keys.

Bill

billsox
Yak Posting Veteran

74 Posts

Posted - 2005-03-01 : 11:39:57

PW -- Tried your code but I've got a table without indexes and it's not showing up in your SELECT result. Any ideas?

Bill

PW
Yak Posting Veteran

95 Posts

Posted - 2005-03-01 : 11:43:32

Forgot - sysindexes is also used for entries for statistics. These are system-generated 'indexes' named _WA_sys_*, so you need to exclude those in the sub-query:

Select name
from sysobjects as o
where type = 'U'
and not exists (
Select *
from sysindexes as i
where o.id = i.id
and i.indid > 0
and i.name not like '_WA_sys_%'
)

[Edit] Failing that, well it's easy to debug isn't it ? If the query doesn't seem to give correct result for a table, just take the object_id('TableName') and see what you find for it in sysindexes that is causing the exception.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources