Friday, June 24, 2011

SQL Table no have index key

I use the below script to search
1. SQL table without clustered index
2. SQL table without any index

-- this only show table without clustered index
SELECT
sys.tables.name,
sys.indexes.name
FROM sys.tables
left join sys.indexes
ON sys.tables.object_id = sys.indexes.object_id
WHERE
isnull(sys.indexes.name,'')=''
and sys.tables.name = 'tablename'
ORDER BY
sys.tables.name

--this will show table without any index
SELECT
a.name,
FROM sys.tables a
left join sys.indexes b
ON a.object_id = b.object_id
WHERE
isnull(b.name,'')=''
--and sys.tables.name = 'PackageServerPathLink'
and not exists (select 1 from sys.indexes c where isnull(name,'')<>''
and c.object_id=b.object_id)
ORDER BY
a.name

No comments: