Foreign Keys without Index - 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

General SQL Server Forums

New to SQL Server Administration

Foreign Keys without Index

Author

Topic

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-03-14 : 02:11:29

Dear Experts,
Does anyone has a script which finds all the foreign keys on all the databases on server that dont have an index on them.

Thanks,
Javeed.

chadmat
The Chadinator

1974 Posts

Posted - 2013-03-14 : 14:12:56

This will work for the current database

select *
from sys.objects o join sys.indexes i on o.parent_object_id = i.object_id
where o.type = 'f'
AND i.type_desc = 'HEAP'
AND o.object_id NOT IN (SELECT o.object_id FROM sys.objects
join sys.indexes i on o.parent_object_id = i.object_id
where o.type = 'f'
AND i.type_desc = 'NONCLUSTERED')

-Chad

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-03-15 : 04:50:59

quote:
Originally posted by chadmat

This will work for the current database

select *
from sys.objects o join sys.indexes i on o.parent_object_id = i.object_id
where o.type = 'f'
AND i.type_desc = 'HEAP'
AND o.object_id NOT IN (SELECT o.object_id FROM sys.objects
join sys.indexes i on o.parent_object_id = i.object_id
where o.type = 'f'
AND i.type_desc = 'NONCLUSTERED')

-Chad

Thanks for that.
I am looking for something which can check on all the databases on the server.

Thanks,
Javeed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-15 : 13:00:00

use sp_Msforeachdb for looping through dbs

try like


EXEC sp_Msforeachdb 'select * 
from sys.objects o join sys.indexes i on o.parent_object_id = i.object_id
where o.type = ''f'' 
AND i.type_desc = ''HEAP''
AND o.object_id NOT IN (SELECT o.object_id FROM sys.objects 
join sys.indexes i on o.parent_object_id = i.object_id
where o.type = ''f'' 
AND i.type_desc = ''NONCLUSTERED'')'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-03-18 : 02:47:38

quote:
Originally posted by visakh16

use sp_Msforeachdb for looping through dbs

try like

EXEC sp_Msforeachdb 'select *
from sys.objects o join sys.indexes i on o.parent_object_id = i.object_id
where o.type = ''f''
AND i.type_desc = ''HEAP''
AND o.object_id NOT IN (SELECT o.object_id FROM sys.objects
join sys.indexes i on o.parent_object_id = i.object_id
where o.type = ''f''
AND i.type_desc = ''NONCLUSTERED'')'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks Visakh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-18 : 12:53:54

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources