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
Go to Top of Page

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.
Go to Top of Page

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/

Go to Top of Page

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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

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

Go to Top of Page
   

- Advertisement -