foreign key - index
-
I understand that an index for foreign key is not automatically created, and must be created manually. Is there a way or command to find out the foreign key that don't have an index?
-
Hi Noelson,
This is part of a script I used to convert some pk-indexes (clusterd/nonclusterd).
Test it for usability for your purpose.
Declare @TbNaam varchar(128)
set @TbNaam = 'T_Tabel'
create table ##Tmp_TbRefsW1 (
cType int not null,
cName sysname not null,
cFlags int not null,
cColCount smallint not null,
cFillFactor smallint null,
cRefTable sysname null,
cRefKey sysname null,
cKeyCol1 sysname null,
cKeyCol2 sysname null,
cKeyCol3 sysname null,
cKeyCol4 sysname null,
cKeyCol5 sysname null,
cKeyCol6 sysname null,
cKeyCol7 sysname null,
cKeyCol8 sysname null,
cKeyCol9 sysname null,
cKeyCol10 sysname null,
cKeyCol11 sysname null,
cKeyCol12 sysname null,
cKeyCol13 sysname null,
cKeyCol14 sysname null,
cKeyCol15 sysname null,
cKeyCol16 sysname null,
cRefCol1 sysname null,
cRefCol2 sysname null,
cRefCol3 sysname null,
cRefCol4 sysname null,
cRefCol5 sysname null,
cRefCol6 sysname null,
cRefCol7 sysname null,
cRefCol8 sysname null,
cRefCol9 sysname null,
cRefCol10 sysname null,
cRefCol11 sysname null,
cRefCol12 sysname null,
cRefCol13 sysname null,
cRefCol14 sysname null,
cRefCol15 sysname null,
cRefCol16 sysname null,
cIndexID smallint null,
cGroupName sysname null,
cDisabled smallint null,
cPrimaryFG smallint null,
cDeleteCascade smallint null,
cUpdateCascade smallint null,
Descending smallint null)
create table ##Tmp_TbRefs (
cType int not null,
cName sysname not null,
cFlags int not null,
cColCount smallint not null,
cFillFactor smallint null,
cRefTable sysname null,
cRefKey sysname null,
cKeyCol1 sysname null,
cKeyCol2 sysname null,
cKeyCol3 sysname null,
cKeyCol4 sysname null,
cKeyCol5 sysname null,
cKeyCol6 sysname null,
cKeyCol7 sysname null,
cKeyCol8 sysname null,
cKeyCol9 sysname null,
cKeyCol10 sysname null,
cKeyCol11 sysname null,
cKeyCol12 sysname null,
cKeyCol13 sysname null,
cKeyCol14 sysname null,
cKeyCol15 sysname null,
cKeyCol16 sysname null,
cRefCol1 sysname null,
cRefCol2 sysname null,
cRefCol3 sysname null,
cRefCol4 sysname null,
cRefCol5 sysname null,
cRefCol6 sysname null,
cRefCol7 sysname null,
cRefCol8 sysname null,
cRefCol9 sysname null,
cRefCol10 sysname null,
cRefCol11 sysname null,
cRefCol12 sysname null,
cRefCol13 sysname null,
cRefCol14 sysname null,
cRefCol15 sysname null,
cRefCol16 sysname null,
cIndexID smallint null,
cGroupName sysname null,
cDisabled smallint null,
cPrimaryFG smallint null,
cDeleteCascade smallint null,
cUpdateCascade smallint null,
Descending smallint null,
ALZTbRef sysname not null)
create table ##Tmp_RefTbs (
cRefTable sysname not null,
cName sysname not null,
creferenced int not null )
create table ##Tmp_TbRefsW1Tb (
cType int not null,
cName sysname not null,
cFlags int not null,
cColCount smallint not null,
cFillFactor smallint null,
cRefTable sysname null,
cRefKey sysname null,
cKeyCol1 sysname null,
cKeyCol2 sysname null,
cKeyCol3 sysname null,
cKeyCol4 sysname null,
cKeyCol5 sysname null,
cKeyCol6 sysname null,
cKeyCol7 sysname null,
cKeyCol8 sysname null,
cKeyCol9 sysname null,
cKeyCol10 sysname null,
cKeyCol11 sysname null,
cKeyCol12 sysname null,
cKeyCol13 sysname null,
cKeyCol14 sysname null,
cKeyCol15 sysname null,
cKeyCol16 sysname null,
cRefCol1 sysname null,
cRefCol2 sysname null,
cRefCol3 sysname null,
cRefCol4 sysname null,
cRefCol5 sysname null,
cRefCol6 sysname null,
cRefCol7 sysname null,
cRefCol8 sysname null,
cRefCol9 sysname null,
cRefCol10 sysname null,
cRefCol11 sysname null,
cRefCol12 sysname null,
cRefCol13 sysname null,
cRefCol14 sysname null,
cRefCol15 sysname null,
cRefCol16 sysname null,
cIndexID smallint null,
cGroupName sysname null,
cDisabled smallint null,
cPrimaryFG smallint null,
cDeleteCascade smallint null,
cUpdateCascade smallint null,
Descending smallint null)
-- initiëel
insert into ##Tmp_TbRefsW1
exec sp_MStablekeys @TbNaam
-- cleanup all but pk-reference
delete from ##Tmp_TbRefsW1
where creftable is not null
-- where did we come from
insert into ##Tmp_TbRefs
select * , @TbNaam from ##Tmp_TbRefsW1
-- get references to this table
insert into ##Tmp_RefTbs
exec sp_MStablerefs @tablename=@TbNaam,@type=null,@direction='foreign',@reftable=null,@flags=null
declare @cRefTb sysname
declare csrRefTbs cursor for select cRefTable from ##Tmp_RefTbs group by cRefTable
open csrRefTbs
FETCH NEXT FROM csrRefTbs
INTO @cRefTb
WHILE @@FETCH_STATUS = 0
BEGIN
-- get references from this table
insert into ##Tmp_TbRefsW1Tb
exec sp_MStablekeys @cRefTb
--remember relationtb
insert into ##Tmp_TbRefs
select * , @cRefTb from ##Tmp_TbRefsW1Tb
where object_id(cRefTable) = object_id(@TbNaam)
-- cleanup
delete from ##Tmp_TbRefsW1Tb
-- next row
FETCH NEXT FROM csrRefTbs
INTO @cRefTb
END
-- Cursor obsolete
CLOSE csrRefTbs
DEALLOCATE csrRefTbs
print 'Begin transaction trxCreateFKix_' + @TbNaam
print ' '
print '-- Create FK indexes'
select 'Create index XFK_' + A.cName + ' on ' + A.ALZTbRef + ' ( '+
A.cKeyCol1
+ case when A.cKeyCol2 is null then '' else ', ' + A.cKeyCol2 end
+ case when A.cKeyCol3 is null then '' else ', ' + A.cKeyCol3 end
+ case when A.cKeyCol4 is null then '' else ', ' + A.cKeyCol4 end
+ case when A.cKeyCol5 is null then '' else ', ' + A.cKeyCol5 end
+ case when A.cKeyCol6 is null then '' else ', ' + A.cKeyCol6 end
+ case when A.cKeyCol7 is null then '' else ', ' + A.cKeyCol7 end
+ case when A.cKeyCol8 is null then '' else ', ' + A.cKeyCol8 end
+ case when A.cKeyCol9 is null then '' else ', ' + A.cKeyCol9 end
+ case when A.cKeyCol10 is null then '' else ', ' + A.cKeyCol10 end
+ case when A.cKeyCol11 is null then '' else ', ' + A.cKeyCol11 end
+ case when A.cKeyCol12 is null then '' else ', ' + A.cKeyCol12 end
+ case when A.cKeyCol13 is null then '' else ', ' + A.cKeyCol13 end
+ case when A.cKeyCol14 is null then '' else ', ' + A.cKeyCol14 end
+ case when A.cKeyCol15 is null then '' else ', ' + A.cKeyCol15 end
+ case when A.cKeyCol16 is null then '' else ', ' + A.cKeyCol16 end
+ ' ) ' + char(13) + 'GO '
from ##Tmp_TbRefs A
inner join ##Tmp_RefTbs B
on A.cName = B.cName
where A.creftable is not null
order by A.creftable, A.cname
print 'Commit transaction trxCreateFKix_' + @TbNaam
-- cleanup
--select * from ##Tmp_TbRefsW1
drop table ##Tmp_TbRefs
drop table ##Tmp_TbRefsW1
drop table ##Tmp_RefTbs
drop table ##Tmp_TbRefsW1Tb
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Nice one. However I am missing a statement somewhere that your script makes calls to undocumented (=unsupported) procedures. You should be able to achieve the same using the documented sp_fkeys and sp_helpindex procedures.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
That's correct ! Sorry for that
I didn't notice because I didn't document that in the script at the time I developed it.
Hey, it's a nice day today. Maybe I'll check to convert to the documented sp-s.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Sounds like a good idea. And while you're at it, please take also care of these:
sp_MShelpcolumns
sp_MStablekeys
sp_MStablechecks
sp_MShelpindex
sp_MShelpfulltextscript
sp_MSobjectprivs
I would like a copy
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Same player shoots again ...
Test this :
'T_ParentMC' = parent table with multicolumn primary key. 18 columns in a PK should do
if object_id('T_ParentMC') is null
begin
create table T_ParentMC (
col1 int identity(1,1) not null ,
col2 int not null,
col3 int not null,
col4 int not null,
col5 varchar(100) not null default ''
, CONSTRAINT PK_ParentMC PRIMARY KEY (col2,col3,col4)
 
print 'table T_ParentMC created'
end
go
if object_id('T_ChildMC') is null
begin
create table T_ChildMC (
col1 int identity(1,1) not null primary key,
col2FK1_1 int not null,
col3FK1_2 int not null,
col4FK1_3 int not null,
col5 varchar(100) not null default ''
, CONSTRAINT FK_C2P FOREIGN KEY (col2FK1_1, col3FK1_2, col4FK1_3)
REFERENCES T_ParentMC (col2,col3,col4)
 
print 'table T_ChildMC created'
end
go
Declare @TbNaam varchar(128)
set @TbNaam = 'T_ParentMC'
SET nocount on
create table #tmpPKeys(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname not null,
TABLE_NAME sysname not null,
COLUMN_NAME sysname not null,
KEY_SEQ smallint not null,
PK_NAME sysname null )
Create index ix#tmpPKeys on #tmpPKeys (TABLE_QUALIFIER, TABLE_OWNER,TABLE_NAME, KEY_SEQ)
-- Get PK-info
insert into #tmpPKeys
exec sp_pkeys @table_name = @TbNaam
-- [ , [ @table_owner = ] 'owner' ]
-- [ , [ @table_qualifier = ] 'qualifier' ] -- DBName
create table #tmpFKeys
(PKTABLE_QUALIFIER sysname not null,
PKTABLE_OWNER sysname not null,
PKTABLE_NAME sysname not null,
PKCOLUMN_NAME sysname not null,
FKTABLE_QUALIFIER sysname not null,
FKTABLE_OWNER sysname not null,
FKTABLE_NAME sysname not null,
FKCOLUMN_NAME sysname not null,
KEY_SEQ smallint not null,
UPDATE_RULE smallint not null,
DELETE_RULE smallint not null,
FK_NAME sysname not null,
PK_NAME sysname not null,
DEFERRABILITY int not null)
Create index #tmpFKeys on #tmpFKeys (FK_NAME, KEY_SEQ)
-- Get FK-info (all dependant objects)
insert into #tmpFKeys
exec sp_fkeys @pktable_name = @TbNaam
-- [ , [ @pktable_owner = ] 'pktable_owner' ]
-- [ , [ @pktable_qualifier = ] 'pktable_qualifier' ]
-- { , [ @fktable_name = ] 'fktable_name' }
-- [ , [ @fktable_owner = ] 'fktable_owner' ]
-- [ , [ @fktable_qualifier = ] 'fktable_qualifier' ]
print 'Begin transaction trxFK2_' + @TbNaam
print ' '
print '-- Create FK-Indexes'
print '-- keep FK column-ordinal / order equal to PK column-ordinal / order (asc/desc)'
select 'Create index [XFK_' + FK1.FK_NAME + '] on [' + FK1.FKTABLE_OWNER + '].[' + FK1.FKTABLE_NAME + '] ( '+
FK1.FKCOLUMN_NAME
+ case when FK2.FKCOLUMN_NAME is null then '' else ', ' + FK2.FKCOLUMN_NAME end
+ case when FK3.FKCOLUMN_NAME is null then '' else ', ' + FK3.FKCOLUMN_NAME end
+ case when FK4.FKCOLUMN_NAME is null then '' else ', ' + FK4.FKCOLUMN_NAME end
+ case when FK5.FKCOLUMN_NAME is null then '' else ', ' + FK5.FKCOLUMN_NAME end
+ case when FK6.FKCOLUMN_NAME is null then '' else ', ' + FK6.FKCOLUMN_NAME end
+ case when FK7.FKCOLUMN_NAME is null then '' else ', ' + FK7.FKCOLUMN_NAME end
+ case when FK8.FKCOLUMN_NAME is null then '' else ', ' + FK8.FKCOLUMN_NAME end
+ case when FK9.FKCOLUMN_NAME is null then '' else ', ' + FK9.FKCOLUMN_NAME end
+ case when FK10.FKCOLUMN_NAME is null then '' else ', ' + FK10.FKCOLUMN_NAME end
+ case when FK11.FKCOLUMN_NAME is null then '' else ', ' + FK11.FKCOLUMN_NAME end
+ case when FK12.FKCOLUMN_NAME is null then '' else ', ' + FK12.FKCOLUMN_NAME end
+ case when FK13.FKCOLUMN_NAME is null then '' else ', ' + FK13.FKCOLUMN_NAME end
+ case when FK14.FKCOLUMN_NAME is null then '' else ', ' + FK14.FKCOLUMN_NAME end
+ case when FK15.FKCOLUMN_NAME is null then '' else ', ' + FK15.FKCOLUMN_NAME end
+ case when FK16.FKCOLUMN_NAME is null then '' else ', ' + FK16.FKCOLUMN_NAME end
+ case when FK17.FKCOLUMN_NAME is null then '' else ', ' + FK17.FKCOLUMN_NAME end
+ case when FK18.FKCOLUMN_NAME is null then '' else ', ' + FK18.FKCOLUMN_NAME end
+ ' ) ' + char(13) + 'GO '
from #tmpFKeys FK1
left join #tmpFKeys FK2
on FK1.FK_NAME = FK2.FK_NAME and FK1.KEY_SEQ = 1 and FK2.KEY_SEQ = 2
left join #tmpFKeys FK3
on FK1.FK_NAME = FK3.FK_NAME and FK1.KEY_SEQ = 1 and FK3.KEY_SEQ = 3
left join #tmpFKeys FK4
on FK1.FK_NAME = FK4.FK_NAME and FK1.KEY_SEQ = 1 and FK4.KEY_SEQ = 4
left join #tmpFKeys FK5
on FK1.FK_NAME = FK5.FK_NAME and FK1.KEY_SEQ = 1 and FK5.KEY_SEQ = 5
left join #tmpFKeys FK6
on FK1.FK_NAME = FK6.FK_NAME and FK1.KEY_SEQ = 1 and FK6.KEY_SEQ = 6
left join #tmpFKeys FK7
on FK1.FK_NAME = FK7.FK_NAME and FK1.KEY_SEQ = 1 and FK7.KEY_SEQ = 7
left join #tmpFKeys FK8
on FK1.FK_NAME = FK8.FK_NAME and FK1.KEY_SEQ = 1 and FK8.KEY_SEQ = 8
left join #tmpFKeys FK9
on FK1.FK_NAME = FK9.FK_NAME and FK1.KEY_SEQ = 1 and FK9.KEY_SEQ = 9
left join #tmpFKeys FK10
on FK1.FK_NAME = FK10.FK_NAME and FK1.KEY_SEQ = 1 and FK10.KEY_SEQ = 10
left join #tmpFKeys FK11
on FK1.FK_NAME = FK11.FK_NAME and FK1.KEY_SEQ = 1 and FK11.KEY_SEQ = 11
left join #tmpFKeys FK12
on FK1.FK_NAME = FK12.FK_NAME and FK1.KEY_SEQ = 1 and FK12.KEY_SEQ = 12
left join #tmpFKeys FK13
on FK1.FK_NAME = FK13.FK_NAME and FK1.KEY_SEQ = 1 and FK13.KEY_SEQ = 13
left join #tmpFKeys FK14
on FK1.FK_NAME = FK14.FK_NAME and FK1.KEY_SEQ = 1 and FK14.KEY_SEQ = 14
left join #tmpFKeys FK15
on FK1.FK_NAME = FK15.FK_NAME and FK1.KEY_SEQ = 1 and FK15.KEY_SEQ = 15
left join #tmpFKeys FK16
on FK1.FK_NAME = FK16.FK_NAME and FK1.KEY_SEQ = 1 and FK16.KEY_SEQ = 16
left join #tmpFKeys FK17
on FK1.FK_NAME = FK17.FK_NAME and FK1.KEY_SEQ = 1 and FK17.KEY_SEQ = 17
left join #tmpFKeys FK18
on FK1.FK_NAME = FK18.FK_NAME and FK1.KEY_SEQ = 1 and FK18.KEY_SEQ = 18
where FK1.KEY_SEQ = 1
order by FK1.FKTABLE_OWNER, FK1.FKTABLE_NAME, FK1.FK_NAME
print 'Commit transaction trxFK2_' + @TbNaam
-- cleanup
drop table #tmpPKeys
drop table #tmpFKeys
-- cleanup when testing is done
-- drop table T_ParentMC
-- drop table T_ChildMC
--
Awaiting the remaks, additions, ...
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
scnr
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks guys for replying...I also would like a copy...
-
Keep in mind that when you have existing indexes with the same column(s), ordinal and direction and you run this script, it will create extra indexes which are redundant, so hurt your system.
You could use http://www.sqlservercentral.com/scripts/contributions/1004.asp to check.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply