SQL Server - Scripts and SPs

Here is a script I came up with several months ago from bits and pieces I picked up here and there. I created a stored procedure with it because it is something that I will run many times to make sure that I have all the indexes I should have on all my databases.


/*******************************************************************************************************
* dbo.proc_BuildMissingFKIndexes
* Creator: Merrill Nelson
*
* Description: Create Indexes for ALL FKs where Index is Missing
* Notes: Executes and prints SQL statement
*
* Usage (Example):
* Exec dbo.proc_BuildMissingFKIndexes
*
* Modifications:
* Developer Name Date Brief Description
* ------------------ -------- ------------------------------------------------------------
* Merrill Nelson 04/04/08 Original Creation
********************************************************************************************************/
Create Procedure [dbo].[proc_BuildMissingFKIndexes]

AS

Declare @WorkTable Table(RID Int Identity(1,1), Query varchar(1000))
Declare @SQL varchar(1000)
,@Counter Int
Insert Into @WorkTable
Select 'If Not Exists (Select 1
From sys.indexes I
Inner Join sys.index_columns IC
On I.[object_id] = IC.[object_id]
Where I.[object_id] = ' + convert(varchar(25),FK.parent_object_id) + '
And I.index_id = IC.index_id
And IC.column_id = ' + convert(varchar(25),FKC.parent_column_id) + '
And objectpropertyex(I.[object_id],''ismsshipped'') = 0
And I.is_hypothetical = 0)
Begin
Create Index FKX_' + (Select SO.[name]
From sys.objects SO
Where SO.[object_id] = FKC.parent_object_id) + '_'
+ (Select SC.[name]
From sys.objects SO
Inner Join sys.columns SC
On SC.[object_id] = SO.[object_id]
Where SO.Type = 'U'
And SO.[object_id] = FKC.[parent_object_id]
And SC.column_id = FKC.parent_column_id) + ' on '
+ (Select SO.[name]
From sys.objects SO
Where SO.[object_id] = FKC.parent_object_id) + '('
+ (Select SC.[name]
From sys.objects SO
Inner Join sys.columns SC
On SC.[object_id] = SO.[object_id]
Where SO.Type = 'U'
And SO.[object_id] = FKC.parent_object_id
And SC.column_id = FKC.parent_column_id) + ')
End'
From sys.foreign_keys FK
Inner Join sys.foreign_key_columns FKC
On FK.[object_id] = FKC.constraint_object_id
Where Not Exists (Select 'an index with same columns and column order'
From sys.indexes I
Inner Join sys.index_columns IC
On I.[object_id] = IC.[object_id]
Where FK.parent_object_id = I.[object_id]
And I.index_id = IC.index_id
-- And FKC.constraint_column_id = IC.key_ordinal
And FKC.parent_column_id = IC.column_id
And objectpropertyex(I.[object_id],'ismsshipped') = 0
And I.is_hypothetical = 0)
And FK.is_ms_shipped = 0
And FK.[name] Not Like 'FK_Audit%'
Order By FK.parent_object_id
Set @Counter = 1
While @Counter <= (Select Max(RID)
From @WorkTable)
Begin
Set @SQL = (Select Query
From @WorkTable
Where RID = @Counter)
Set @Counter = @Counter + 1
Print @SQL
Exec(@SQL)
End

Format it so that it meets your standards and Enjoy!
- Merrill