Wednesday, May 17, 2006

List All Tables Without An Index

Here are 2 quick ways to list all tables in your database without an index
The first query is using sysobjects and the second query is using the INFORMATION_SCHEMA.TABLES view

SELECT name AS TableName
FROM sysobjects s
WHERE type = 'U'
AND OBJECTPROPERTY(id , 'TableHasIndex' ) = 0
ORDER BY TableName


SELECT TABLE_NAME AS TableName
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE ='BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME) , 'TableHasIndex' ) = 0
ORDER BY TableName

No comments: