Finding columns which are FKs and having no indexes

Question

Post reply

Finding columns which are FKs and having no indexes

NewBeeSQL

SSCrazy

Points: 2170
More actions
March 3, 2010 at 4:51 am

#221232

Folks..
I need a sql query which can give me the list of Foreign Key column names along with table name which does not have indexes in a database.
TIA
NBSql
-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

SQL 2005

Select s.name +'.'+ o.name as TableName,o2.name as FKName,c.name as FKColumn

From sys.foreign_key_columns fk

Inner Join sys.objects o

On o.object_id = fk.parent_object_id

Inner Join sys.schemas s

On s.schema_id = o.schema_id

Inner Join sys.objects o2

On o2.object_id = fk.constraint_object_id

Inner Join sys.columns c

On fk.parent_object_id = c.object_id

And fk.parent_column_id = c.column_id

Left Outer Join sys.index_columns ic

On ic.object_id = fk.parent_object_id

And ic.column_id = fk.parent_column_id

And ic.index_column_id = fk.constraint_column_id

Where ic.object_id is null

Order by o.name

SQL 2000 (but will work on 2005 still).

select o.name as TableName,c.name as FKColumn from sys.sysreferences r

Inner join sys.syscolumns c

on r.fkeyid = c.id

and r.fkey1 = c.colid

Inner Join sys.sysobjects o

on c.id = o.id

Left Outer Join sys.sysindexkeys k

on c.id = k.id

And c.colid = k.colid

Where k.id is null

Order by o.name

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 2

Thanks for the query. It works great.........

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

You're welcome

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 4

One more question which i missed in Original Post..

Does it require to have an index on FK columns. can any one please post possitives and negatives of having an index on FK columns..

TIA...

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

NewBeeSQL (3/3/2010)
One more question which i missed in Original Post..
Does it require to have an index on FK columns. can any one please post possitives and negatives of having an index on FK columns..
TIA...

Here's an article on the subject

http://www.sqlservercentral.com/articles/T-SQL/68337/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events