InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 17:19:56 GMT20http://www.sqlservercentral.com/Forums/Topic569970-360-1.aspx...and most of all thanks Semko!Wed, 30 Jan 2013 06:19:53 GMTnjaalehttp://www.sqlservercentral.com/Forums/Topic569970-360-1.aspxAnd even 2.5 years later:Thanks hanskappert!NjålWed, 30 Jan 2013 06:15:56 GMTnjaalehttp://www.sqlservercentral.com/Forums/Topic569970-360-1.aspxPlease note: 2 year old thread.Fri, 27 Aug 2010 05:45:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic569970-360-1.aspxNice script. Is was almost what i was looking for. I added an extra column to the output, containing the DDL to create the missing index:IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1IF OBJECT_ID('tempdb..#FKTable') IS NOT NULL DROP TABLE #FKTable--Create index temp tableCREATE TABLE #t1 ( do integer default(0), index_name varchar(100), index_descrip varchar(200), index_keys varchar(200), table_name varchar(100)) --Create FK temp table CREATE TABLE #FKTable ( fk_name varchar(100), fk_keys varchar(200), fk_keyno int, table_name varchar(100)) --Collect and uppdate all index info EXEC sp_msforeachtable "insert #t1 (index_name, index_descrip, index_keys) exec sp_helpindex '?'; update #t1 set table_name = '?', do = 1 where do = 0"UPDATE #t1 SET table_name = replace(table_name , '[', '')UPDATE #t1 set table_name = replace(table_name , ']', '') --Collect all index info INSERT INTO #FKTableSELECT OBJECT_NAME(constid) AS FKName, COL_NAME(fkeyid, fkey) AS FKColumn, keyno, s.name + '.' + OBJECT_NAME(fkeyid) AS TabNameFROM sysforeignkeys k JOIN sys.objects c ON k.constid = c.object_idJOIN sys.schemas sON c.schema_id = s.schema_id --If FK have two or more columns add them in one row to be able to compare with index columns. DECLARE @FKName AS VARCHAR(200), @FKColumn as VARCHAR(100)DECLARE FKCurusor CURSOR FORSELECT OBJECT_NAME(constid) AS FKName, COL_NAME(fkeyid, fkey) AS FKColumnFROM sysforeignkeys k JOIN sysobjects c ON k.constid = c.idWHERE keyno > 1ORDER BY keyno DELETE FROM #FKTable WHERE fk_keyno > 1 OPEN FKCurusor FETCH NEXT FROM FKCurusor INTO @FKName,@FKColumn WHILE (@@FETCH_STATUS = 0) BEGIN UPDATE #FKTable SET fk_keys = fk_keys + ', ' + @FKColumn WHERE fk_name = @FKName FETCH NEXT FROM FKCurusor INTO @FKName,@FKColumn END CLOSE FKCurusorDEALLOCATE FKCurusor/*SELECT * FROM #FKTableORDER BY table_nameSELECT * FROM #t1ORDER BY table_name*/PRINT '---------------------------------------------------------------------FK MISSING Indexes----------------------------------------------------------------------------'SELECT DISTINCT table_name,fk_keys, 'CREATE NONCLUSTERED INDEX [' + fk_name + '] ON ' + table_name + '(' +fk_keys + ' ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'FROM #FKTable f1WHERE NOT EXISTS (SELECT fk_name FROM #FKTable fINNER JOIN #t1 tON f.table_name = t.table_nameWHERE (f1.fk_name = f.fk_nameAND fk_keys = index_keys)OR ( f1.fk_name = f.fk_nameAND fk_keys = SUBSTRING (index_keys, 1 , CASE WHEN CHARINDEX( ',',index_keys)= 0 THEN 0 ELSE CHARINDEX( ',',index_keys) -1END)))Fri, 27 Aug 2010 05:25:52 GMThanskapperthttp://www.sqlservercentral.com/Forums/Topic569970-360-1.aspxThanks Jack, your script will return 25 rows from AdventureWorks database. Script I wrote returns 68 rows. Why, I'm looking on all columns included in index and want to have same construction as FK, even if FK include more then one column. This script return the same result as yours, but it isn't what I want. [code]SELECT OBJECT_NAME(parent_object_id) AS table_name, OBJECT_NAME(constraint_object_id) AS fk_nameFROM sys.foreign_key_columns AS fkcLEFT JOIN sys.index_columns AS ic ON fkc.parent_object_id = ic.object_id AND fkc.parent_column_id = ic.column_idWHERE ic.object_id IS NULLORDER BY table_name, fk_name[/code]Just done one change in my first script, now it will look even if you have an index there first column are same as FK first column.[code]IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1IF OBJECT_ID('tempdb..#FKTable') IS NOT NULL DROP TABLE #FKTable--Create index temp tableCREATE TABLE #t1 ( do integer default(0), index_name varchar(100), index_descrip varchar(200), index_keys varchar(200), table_name varchar(100)) --Create FK temp table CREATE TABLE #FKTable ( fk_name varchar(100), fk_keys varchar(200), fk_keyno int, table_name varchar(100)) --Collect and uppdate all index info EXEC sp_msforeachtable "insert #t1 (index_name, index_descrip, index_keys) exec sp_helpindex '?'; update #t1 set table_name = '?', do = 1 where do = 0"UPDATE #t1 SET table_name = replace(table_name , '[', '')UPDATE #t1 set table_name = replace(table_name , ']', '') --Collect all index info INSERT INTO #FKTableSELECT OBJECT_NAME(constid) AS FKName, COL_NAME(fkeyid, fkey) AS FKColumn, keyno, s.name + '.' + OBJECT_NAME(fkeyid) AS TabNameFROM sysforeignkeys k JOIN sys.objects c ON k.constid = c.object_idJOIN sys.schemas sON c.schema_id = s.schema_id --If FK have two or more columns add them in one row to be able to compare with index columns. DECLARE @FKName AS VARCHAR(200), @FKColumn as VARCHAR(100)DECLARE FKCurusor CURSOR FORSELECT OBJECT_NAME(constid) AS FKName, COL_NAME(fkeyid, fkey) AS FKColumnFROM sysforeignkeys k JOIN sysobjects c ON k.constid = c.idWHERE keyno > 1ORDER BY keyno DELETE FROM #FKTable WHERE fk_keyno > 1 OPEN FKCurusor FETCH NEXT FROM FKCurusor INTO @FKName,@FKColumn WHILE (@@FETCH_STATUS = 0) BEGIN UPDATE #FKTable SET fk_keys = fk_keys + ', ' + @FKColumn WHERE fk_name = @FKName FETCH NEXT FROM FKCurusor INTO @FKName,@FKColumn END CLOSE FKCurusorDEALLOCATE FKCurusor/*SELECT * FROM #FKTableORDER BY table_nameSELECT * FROM #t1ORDER BY table_name*/PRINT '---------------------------------------------------------------------FK MISSING Indexes----------------------------------------------------------------------------'SELECT DISTINCT table_name, fk_name FROM #FKTable f1WHERE NOT EXISTS (SELECT fk_name FROM #FKTable fINNER JOIN #t1 tON f.table_name = t.table_nameWHERE (f1.fk_name = f.fk_nameAND fk_keys = index_keys)OR ( f1.fk_name = f.fk_nameAND fk_keys = SUBSTRING (index_keys, 1 , CASE WHEN CHARINDEX( ',',index_keys)= 0 THEN 0 ELSE CHARINDEX( ',',index_keys) -1END)))[/code]Regards/SemkoThu, 18 Sep 2008 01:43:20 GMTSemko Redzichttp://www.sqlservercentral.com/Forums/Topic569970-360-1.aspxI understood what you were looking for, which is why I said that the script I linked to would be a starting point. If you use the script I linked to and the other system views I mention you can get the information you are looking for in one query. Something like:[code][font="Courier New"][size="2"][color="blue"]SELECT    [/color][color="black"]RC.Constraint_Name [/color][color="blue"]AS [/color][color="black"]FK_Constraint[/color][color="gray"],   [/color][color="black"]RC.Constraint_Catalog [/color][color="blue"]AS [/color][color="black"]FK_Database[/color][color="gray"],   [/color][color="black"]RC.Constraint_Schema [/color][color="blue"]AS [/color][color="black"]FK_Schema[/color][color="gray"],   [/color][color="black"]CCU.Table_Name [/color][color="blue"]AS [/color][color="black"]FK_Table[/color][color="gray"],   [/color][color="black"]CCU.Column_Name [/color][color="blue"]AS [/color][color="black"]FK_Column[/color][color="blue"]FROM    [/color][color="black"]information_schema.referential_constraints RC [/color][color="blue"]JOIN   [/color][color="black"]INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU [/color][color="blue"]ON        [/color][color="black"]RC.CONSTRAINT_NAME [/color][color="blue"]= [/color][color="black"]CCU.CONSTRAINT_NAME [/color][color="blue"]JOIN   [/color][color="black"]INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU2 [/color][color="blue"]ON       [/color][color="black"]RC.UNIQUE_CONSTRAINT_NAME [/color][color="blue"]= [/color][color="black"]CCU2.CONSTRAINT_NAME [/color][color="magenta"]LEFT [/color][color="blue"]JOIN   [/color][color="black"]sys.columns C [/color][color="blue"]ON       [/color][color="black"]CCU.Column_Name [/color][color="blue"]= [/color][color="black"]C.name [/color][color="gray"]AND       [/color][color="black"]CCU.Table_Name [/color][color="blue"]= [/color][color="magenta"]OBJECT_NAME[/color][color="gray"]([/color][color="black"]C.[/color][color="magenta"]OBJECT_ID[/color][color="gray"]) [/color][color="magenta"]LEFT [/color][color="blue"]JOIN   [/color][color="black"]sys.index_columns IC [/color][color="blue"]ON       [/color][color="black"]C.[/color][color="magenta"]OBJECT_ID [/color][color="blue"]= [/color][color="black"]IC.[/color][color="magenta"]OBJECT_ID [/color][color="gray"]AND       [/color][color="black"]C.column_id [/color][color="blue"]= [/color][color="black"]IC.column_id [/color][color="magenta"]LEFT [/color][color="blue"]JOIN   [/color][color="black"]sys.indexes I [/color][color="blue"]ON       [/color][color="black"]IC.[/color][color="magenta"]OBJECT_ID [/color][color="blue"]= [/color][color="black"]I.[/color][color="magenta"]OBJECT_ID [/color][color="gray"]AND       [/color][color="black"]IC.index_Id [/color][color="blue"]= [/color][color="black"]I.index_Id [/color][color="blue"]WHERE   [/color][color="black"]I.name [/color][color="blue"]IS [/color][color="gray"]NULL[/color][color="blue"]ORDER BY   [/color][color="black"]RC.Constraint_NAME     [/color][/size][/font][/code]This is not perfected, but it does do what I think you want it to do. It will show any tables with Foreign Keys where any column of that foreign key does not have an index on it.Wed, 17 Sep 2008 07:58:57 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic569970-360-1.aspxThanks for your replay Jack, the result I want is to return a list of all tables, which have FK and are missing an index on same columns as FK. Code to solution I want to have is: --CODE STARTIF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1IF OBJECT_ID('tempdb..#FKTable') IS NOT NULL DROP TABLE #FKTable--Create index temp tableCREATE TABLE #t1 ( do integer default(0), index_name varchar(100), index_descrip varchar(200), index_keys varchar(200), table_name varchar(100)) --Create FK temp table CREATE TABLE #FKTable ( fk_name varchar(100), fk_keys varchar(200), fk_keyno int, table_name varchar(100)) --Collect and uppdate all index info EXEC sp_msforeachtable "insert #t1 (index_name, index_descrip, index_keys) exec sp_helpindex '?'; update #t1 set table_name = '?', do = 1 where do = 0"UPDATE #t1 SET table_name = replace(table_name , '[', '')UPDATE #t1 set table_name = replace(table_name , ']', '') --Collect all index info INSERT INTO #FKTableSELECT OBJECT_NAME(constid) AS FKName, COL_NAME(fkeyid, fkey) AS FKColumn, keyno, s.name + '.' + OBJECT_NAME(fkeyid) AS TabNameFROM sysforeignkeys k JOIN sys.objects c ON k.constid = c.object_idJOIN sys.schemas sON c.schema_id = s.schema_id --If FK have two or more columns add them in one row to be able to compare with index columns. DECLARE @FKName AS VARCHAR(200), @FKColumn as VARCHAR(100)DECLARE FKCurusor CURSOR FORSELECT OBJECT_NAME(constid) AS FKName, COL_NAME(fkeyid, fkey) AS FKColumnFROM sysforeignkeys k JOIN sysobjects c ON k.constid = c.idWHERE keyno > 1ORDER BY keyno DELETE FROM #FKTable WHERE fk_keyno > 1 OPEN FKCurusor FETCH NEXT FROM FKCurusor INTO @FKName,@FKColumn WHILE (@@FETCH_STATUS = 0) BEGIN UPDATE #FKTable SET fk_keys = fk_keys + ', ' + @FKColumn WHERE fk_name = @FKName FETCH NEXT FROM FKCurusor INTO @FKName,@FKColumn END CLOSE FKCurusorDEALLOCATE FKCurusor/*SELECT * FROM #FKTableORDER BY table_nameSELECT * FROM #t1ORDER BY table_name*/PRINT '---------------------------------------------------------------------FK MISSING Indexes----------------------------------------------------------------------------'SELECT DISTINCT table_name, fk_name FROM #FKTable f1WHERE NOT EXISTS (SELECT fk_name FROM #FKTable fINNER JOIN #t1 tON f.table_name = t.table_nameWHERE f1.fk_name = f.fk_nameAND fk_keys = index_keys)--CODE ENDRegards /SemkoWed, 17 Sep 2008 07:22:25 GMTSemko Redzichttp://www.sqlservercentral.com/Forums/Topic569970-360-1.aspxHere is a link to a script I submitted that will find all foreign keys:[url]http://www.sqlservercentral.com/scripts/foreign+keys/64333/[/url]It wouldn't be too hard to extend it using sys.index_columns and sys.indexesTue, 16 Sep 2008 06:51:51 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic569970-360-1.aspxHi, just now I'm working with performance tuning on a SQL Server 2005. I need an script that can find all Foreign Key missing Index. I mean I want to find all tables which have Foreign Key and are missing an index on same columns which are included in Foreign Key. Regards/SemkoTue, 16 Sep 2008 00:50:49 GMTSemko Redzic