Hi,
I need to find out all the tables in database, which has FK columns and dont have any Non-clustered index on them.
Kindly help.
Tables with FK column without non-clustered index
October 22nd, 2014 5:24am
Use sys.foreign_keys
see
Free Windows Admin Tool Kit Click here and download it now
October 22nd, 2014 5:39am
I used ,
SELECT OBJECT_NAME(f.parent_object_id) TableName, COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName ,f.name FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id
but I need to add which column has FK columns and dont have any Non-clustered index on them.
Thanks
October 22nd, 2014 5:42am
--------------no indexes--------
WITH fk_no_indexes
AS
(
SELECT
[constraint_name] = f.[name],
[child_table] = OBJECT_NAME(f.parent_object_id),
[child_table_id]=f.parent_object_id,
[child_column] = cc.name,
[child_column_id]=cc.[column_id],
[parent_table] = OBJECT_NAME(f.referenced_object_id),
[parent_column] = pc.name
FROM
sys.foreign_keys f
INNER JOIN
(
SELECT
c.[object_id],
c.name,
c.column_id,
ic.index_id
FROM
sys.columns c
INNER JOIN
sys.index_columns ic
ON
c.[object_id] = ic.[object_id]
AND c.column_id = ic.column_id
) AS pc
ON
f.key_index_id = pc.index_id
INNER JOIN
sys.foreign_key_columns fkc
ON
f.[object_id] = fkc.constraint_object_id
AND pc.[object_id] = fkc.referenced_object_id
AND fkc.referenced_column_id = pc.column_id
INNER JOIN
sys.columns cc
ON
fkc.parent_object_id = cc.[object_id]
AND fkc.parent_column_id = cc.column_id
)
SELECT [constraint_name],[child_table],[child_column],[parent_table] FROM
fk_no_indexes WHERE NOT EXISTS
(SELECT * FROM sys.index_columns i
WHERE i.[object_id]=[child_table_id]
AND [child_column_id]=column_id
)
ORDER BY
constraint_name,
child_table;
WITH fk_no_indexes
AS
(
SELECT
[constraint_name] = f.[name],
[child_table] = OBJECT_NAME(f.parent_object_id),
[child_table_id]=f.parent_object_id,
[child_column] = cc.name,
[child_column_id]=cc.[column_id],
[parent_table] = OBJECT_NAME(f.referenced_object_id),
[parent_column] = pc.name
FROM
sys.foreign_keys f
INNER JOIN
(
SELECT
c.[object_id],
c.name,
c.column_id,
ic.index_id
FROM
sys.columns c
INNER JOIN
sys.index_columns ic
ON
c.[object_id] = ic.[object_id]
AND c.column_id = ic.column_id
) AS pc
ON
f.key_index_id = pc.index_id
INNER JOIN
sys.foreign_key_columns fkc
ON
f.[object_id] = fkc.constraint_object_id
AND pc.[object_id] = fkc.referenced_object_id
AND fkc.referenced_column_id = pc.column_id
INNER JOIN
sys.columns cc
ON
fkc.parent_object_id = cc.[object_id]
AND fkc.parent_column_id = cc.column_id
)
SELECT [constraint_name],[child_table],[child_column],[parent_table] FROM
fk_no_indexes WHERE NOT EXISTS
(SELECT * FROM sys.index_columns i
WHERE i.[object_id]=[child_table_id]
AND [child_column_id]=column_id
)
ORDER BY
constraint_name,
child_table;
Free Windows Admin Tool Kit Click here and download it now
October 22nd, 2014 5:43am
You can use the script in this link: How can I find unindexed foreign keys in SQL Server
October 22nd, 2014 6:05am
I need output something like this;
| TableName | ColumnName | ForeignKeys | Index Name | Index type |
as I mentioned above I able to find tableName,columnName Foreing on them using
SELECT OBJECT_NAME(f.parent_object_id) TableName, COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName ,f.name FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id
but I need Index Name with index type in this query.
Free Windows Admin Tool Kit Click here and download it now
October 22nd, 2014 6:15am
Are you looking for the columns that do not have an index? Do you want to see on the fk a clustered index?
October 22nd, 2014 6:28am
I need a status of indexes on foreign key column.
so that I can further divied that data into non clustered ,clusterd indexes
| TableName | ColumnName | ForeignKeys | Index Name | Index type |
Thanks
Free Windows Admin Tool Kit Click here and download it now
October 22nd, 2014 7:04am
I don't have any query canned for the purpose, nor the time to compose one right now. (It's not a trivial problem, when it comes to multi-column keys.)
However, I would like to point out that once you have compiled this informtion, you should use it judiciously. Not having an index on an FK column can indeed cause performance issue, particularly with delete operations. But if deletions are out of the question, and nor there is any need to find data per this FK, any index may only add update over on the parent table.
October 22nd, 2014 7:27am
Are you looking for the columns that do not have an index? Do you want to see on the fk a cluste
Free Windows Admin Tool Kit Click here and download it now
October 22nd, 2014 7:34am
I need a status of indexes on foreign key column.
so that I can further divied that data into non clustered ,clusterd indexes
TableName ColumnName ForeignKeys Index Name Index type
October 22nd, 2014 7:34am
I couldn't find what I needed on the web, so I wrote my own and posted it here: Identify Foreign Keys without Corresponding Indexes. The query will spit out a list of foreign keys that do not have corresponding indexes, along with comma-separated column lists for the foreign keys.
Best of luck!
Free Windows Admin Tool Kit Click here and download it now
April 23rd, 2015 6:26pm
This topic is archived. No further replies will be accepted.
Other recent topics
