September 7, 2011 at 4:49 am
Hi,
How to check the foreign key column index created or not?
Any one can give a idea about this.
thanks
Ram
September 7, 2011 at 5:48 am
When you create a foreign key, it's a constraint, not an index. If you want an index on a foreign key, that must be created separately.
You can look at the system tables sys.foreign_keys and sys.foreign_keys_columns to see what's been created.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 7, 2011 at 6:13 am
As Grant suggested these are two separate . However you can think for something to get a relation...
Select A.name,OBJECT_NAME(A.parent_object_id),key_index_id,B.type_DESC From sys.foreign_keys a
INNER JOIN sys.indexes b oN a.parent_object_id = b.object_id AND a.key_index_id = B.index_id
September 7, 2011 at 6:27 am
sqlzealot-81 (9/7/2011)
As Grant suggested these are two separate . However you can think for something to get a relation...
Select A.name,OBJECT_NAME(A.parent_object_id),key_index_id,B.type_DESC From sys.foreign_keys aINNER JOIN sys.indexes b oN a.parent_object_id = b.object_id AND a.key_index_id = B.index_id
Err, no. The key_index_id column in sys.foreign_keys is the ID of the unique index in the referenced table, not the id of an index that may or may not have been created on the foreign key column in the referencing table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 7, 2011 at 6:31 am
Ohhh Okei, I was not able to find a relation. Thanks for correcting me. My sincere apologies .I own my mistake.
September 7, 2011 at 10:46 am
You can determine what FK's do not have an index on the column with a query like this:
SELECT
OBJECT_NAME(IC.object_id, DB_ID()) AS table_name,
FK.name AS fk_name,
FK.type_desc,
I.name AS index_name,
I.type_desc,
FK.create_date,
FK.modify_date,
FK.is_disabled,
FK.is_not_for_replication,
FK.is_not_trusted,
FK.delete_referential_action,
FK.delete_referential_action_desc,
FK.update_referential_action,
FK.update_referential_action_desc,
IC.index_id,
IC.index_column_id,
IC.key_ordinal,
I.is_unique,
I.ignore_dup_key,
I.is_primary_key,
I.is_unique_constraint,
I.fill_factor
FROM
sys.foreign_keys AS FK
JOIN sys.foreign_key_columns AS FKC
ON FK.object_id = FKC.constraint_object_id
LEFT JOIN sys.index_columns AS IC
JOIN sys.indexes AS I
ON IC.index_id = I.index_id AND
IC.object_id = I.object_id
ON FKC.parent_object_id = IC.object_id AND
FKC.parent_column_id = IC.column_id
Keep in mind that that this does not consider if the column is the leading column in the index, so you can't say for sure if the index will be relevant to the FK.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 13, 2011 at 12:52 pm
here's my take on it;
this query featuring a CTE lists all foreign keys, and if there is an index with the leading column on the child table or not.
if it exists, you can see some of it in the data, otherwise it creates a PotentialIndex statement in a different column for output...i did take Jacks caveat into consideration about the leading column of an index being critical for the join., and i completely ignored multi column foreign keys as too much work without someone cutting a check fo rhte time to fiddle with it....
for a practical applicaiton, i think i'd only add an index like this on tables with lots of rows in it, but
a bit 'o' feedback would be appreciated.
--select * from sys.index_columns
WITH MyIndexes
AS (SELECT
DISTINCT
idxz.name AS IndexName,
idxz.object_id,
OBJECT_NAME(idxz.object_id) AS TableName,
ixcolnamez.name AS IndexColumnName
FROM
sys.indexes idxz
INNER JOIN sys.index_columns idxcolz
ON idxz.index_id = idxcolz.index_id
INNER JOIN sys.columns ixcolnamez
ON idxcolz.object_id = ixcolnamez.object_id
AND idxz.object_id = ixcolnamez.object_id
AND idxcolz.column_id = ixcolnamez.column_id
WHERE
index_column_id = 1 --because only the lead column will help for the join
)
SELECT
SCHEMA_NAME(chldz.schema_id) AS SchemaName,
objz.name AS FKName,
parenz.name AS ParentTable,
pcolz.name AS ParentColumn,
chldz.name AS ChildTable,
ccolz.name AS ChildColumn,
MyIndexes.*,
CASE
WHEN MyIndexes.object_id IS NULL
THEN 'CREATE INDEX [IX_FK_' + chldz.name + '_' + ccolz.name + '] ON ' + QUOTENAME(SCHEMA_NAME(chldz.schema_id)) + '.' + QUOTENAME(chldz.name) + '(' + QUOTENAME(ccolz.name) + ');'
ELSE ''
END AS PotentialIndex
FROM
sys.sysforeignkeys fkeyz
INNER JOIN sys.objects objz
ON fkeyz.constid = objz.object_id
INNER JOIN sys.objects parenz
ON fkeyz.rkeyid = parenz.object_id
INNER JOIN sys.columns pcolz
ON parenz.object_id = pcolz.object_id
AND fkeyz.rkey = pcolz.column_id
INNER JOIN sys.objects chldz
ON fkeyz.fkeyid = chldz.object_id
INNER JOIN sys.columns ccolz
ON chldz.object_id = ccolz.object_id
AND fkeyz.fkey = ccolz.column_id
LEFT OUTER JOIN MyIndexes
ON chldz.object_id = MyIndexes.object_id
AND ccolz.name = MyIndexes.IndexColumnName
Lowell
September 13, 2011 at 1:29 pm
Lowell (9/13/2011)
here's my take on it;this query featuring a CTE lists all foreign keys, and if there is an index with the leading column on the child table or not.
if it exists, you can see some of it in the data, otherwise it creates a PotentialIndex statement in a different column for output...i did take Jacks caveat into consideration about the leading column of an index being critical for the join., and i completely ignored multi column foreign keys as too much work without someone cutting a check fo rhte time to fiddle with it....
for a practical applicaiton, i think i'd only add an index like this on tables with lots of rows in it, but
a bit 'o' feedback would be appreciated.
--select * from sys.index_columns
WITH MyIndexes
AS (SELECT
DISTINCT
idxz.name AS IndexName,
idxz.object_id,
OBJECT_NAME(idxz.object_id) AS TableName,
ixcolnamez.name AS IndexColumnName
FROM
sys.indexes idxz
INNER JOIN sys.index_columns idxcolz
ON idxz.index_id = idxcolz.index_id
INNER JOIN sys.columns ixcolnamez
ON idxcolz.object_id = ixcolnamez.object_id
AND idxz.object_id = ixcolnamez.object_id
AND idxcolz.column_id = ixcolnamez.column_id
WHERE
index_column_id = 1 --because only the lead column will help for the join
)
SELECT
SCHEMA_NAME(chldz.schema_id) AS SchemaName,
objz.name AS FKName,
parenz.name AS ParentTable,
pcolz.name AS ParentColumn,
chldz.name AS ChildTable,
ccolz.name AS ChildColumn,
MyIndexes.*,
CASE
WHEN MyIndexes.object_id IS NULL
THEN 'CREATE INDEX [IX_FK_' + chldz.name + '_' + ccolz.name + '] ON ' + QUOTENAME(SCHEMA_NAME(chldz.schema_id)) + '.' + QUOTENAME(chldz.name) + '(' + QUOTENAME(ccolz.name) + ');'
ELSE ''
END AS PotentialIndex
FROM
sys.sysforeignkeys fkeyz
INNER JOIN sys.objects objz
ON fkeyz.constid = objz.object_id
INNER JOIN sys.objects parenz
ON fkeyz.rkeyid = parenz.object_id
INNER JOIN sys.columns pcolz
ON parenz.object_id = pcolz.object_id
AND fkeyz.rkey = pcolz.column_id
INNER JOIN sys.objects chldz
ON fkeyz.fkeyid = chldz.object_id
INNER JOIN sys.columns ccolz
ON chldz.object_id = ccolz.object_id
AND fkeyz.fkey = ccolz.column_id
LEFT OUTER JOIN MyIndexes
ON chldz.object_id = MyIndexes.object_id
AND ccolz.name = MyIndexes.IndexColumnName
Nice work Lowell.
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
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply