Τα foreign keys όπως είναι γνωστό δεν φτιάχνουν κάποιον index στο reference table. Επίσης είναι γνωστό ότι καλό (αλλά όχι πάντα απαραίτητο) να φτιάχνουμε nonclustered index στο reference table στo field(s) που εμπλέκονται στο FK constraint.
Με το παρακάτω script βρίσκουμε αυτές τις περιπτώσεις τις αξιολογούμε και αν χρειαστεί να φτιάξουμε αυτούς η τελευταία κολώνα περιέχει και το create index statement.
Enjoy
//antonch
with fcc as
(
select
sch1.name as parent_schema_name,
object_name(fkc.parent_object_id) as parent_table_name,
object_name(fkc.constraint_object_id) as constraint_name,
sch2.name as referenced_schema,
object_name(fkc.referenced_object_id) as referenced_table_name,
substring(
( select ','
+ rtrim(col_name(fc.parent_object_id,parent_column_id)) as [data()]
from sys.foreign_key_columns as fc with (nolock)
inner join sys.foreign_keys as fk with (nolock) on fc.constraint_object_id = fk.[object_id]
and fc.constraint_object_id = fkc.constraint_object_id
order by fc.constraint_column_id
for xml path('')
), 2, 8000) as parent_columns,
substring(
( select ','
+ rtrim(col_name(fc.referenced_object_id,referenced_column_id)) as [data()]
from sys.foreign_key_columns as fc with (nolock)
inner join sys.foreign_keys as fk with (nolock) on fc.constraint_object_id = fk.[object_id]
and fc.constraint_object_id = fkc.constraint_object_id
order by constraint_column_id
for xml path('')
), 2, 8000) as referenced_columns
from sys.foreign_key_columns as fkc with (nolock)
inner join sys.objects as obj1 with (nolock) on fkc.parent_object_id = obj1.[object_id]
inner join sys.tables as tbl1 with (nolock) on tbl1.[object_id] = obj1.[object_id]
inner join sys.schemas as sch1 with (nolock) on sch1.[schema_id] = tbl1.[schema_id]
inner join sys.objects as obj2 with (nolock) on fkc.referenced_object_id = obj2.[object_id]
inner join sys.tables as tbl2 with (nolock) on tbl2.[object_id] = obj2.[object_id]
inner join sys.schemas as sch2 with (nolock) on sch2.[schema_id] = tbl2.[schema_id]
where obj1.type = 'U'
and
obj2.type = 'U'
group by obj1.[schema_id],
obj2.[schema_id],
fkc.parent_object_id,
constraint_object_id,
referenced_object_id,
sch1.name,
sch2.name
),
idxcols as
(
select
s.name as schemaname,
object_name(t.[object_id]) as objectname,
substring(
(
select ','
+ rtrim(ac.name)
from sys.tables as st
inner join sys.indexes as ix on st.[object_id] = ix.[object_id]
inner join sys.index_columns as ic on ix.[object_id] = ic.[object_id] and ix.[index_id] = ic.[index_id]
inner join sys.all_columns as ac on st.[object_id] = ac.[object_id] and ic.[column_id] = ac.[column_id]
where i.[object_id] = ix.[object_id]
and
i.index_id = ix.index_id
and
ic.is_included_column = 0
order by ac.column_id
for xml path('')
), 2, 8000 ) as keycols
from sys.indexes as i
inner join sys.tables as t on t.[object_id] = i.[object_id]
inner join sys.schemas as s on s.[schema_id] = t.[schema_id]
where i.[type] in (1,2,5,6)
and
i.is_unique_constraint = 0
and
t.is_ms_shipped = 0
)
select fcc.constraint_name,
fcc.parent_schema_name +'.' + fcc.parent_table_name as parent_table,
fcc.referenced_schema + '.' + fcc.referenced_table_name as reference_table,
fcc.parent_columns,
fcc.referenced_columns,
N'CREATE NONCLUSTERED INDEX idx_' +
fcc.referenced_table_name +
'_' +
fcc.constraint_name +
N' ON ' +
fcc.parent_schema_name +
'.' +
fcc.parent_table_name +
N'(' +
fcc.referenced_columns +
N');' as ddl_create
from fcc
where not exists ( SELECT 1 FROM idxcols
WHERE fcc.parent_schema_name = idxcols.schemaName
AND fcc.parent_table_name = idxcols.objectName
AND REPLACE(fcc.parent_columns,'' ,'') = idxcols.KeyCols)