T-SQL Foreign Key (FK) Index Creator


/*
FOREIGN KEY INDEX CREATOR

THIS SCRIPT GENERATES SQL TO DROPS AND RECREATE NON-CLUSTERED INDEXES ON ALL FK COLUMNS
SET @FK_PREFIX TO MATCH THE FOREIGN KEY PREFIX IN YOUR DATABASE.

SQL2005 AND ABOVE ONLY (SYS VIEWS)
*/

DECLARE @FK_PREFIX NVARCHAR(50)
SET @FK_PREFIX = 'FK_'

DECLARE @CONSTRAINT_NAME NVARCHAR(255)
DECLARE @TABLE_SCHEMA NVARCHAR(255)
DECLARE @TABLE_NAME NVARCHAR(255)
DECLARE @COLUMN_NAME NVARCHAR(255)

DECLARE FK_INDEX_CURSOR CURSOR READ_ONLY
FOR SELECT
CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE

OPEN FK_INDEX_CURSOR
FETCH NEXT FROM FK_INDEX_CURSOR INTO @CONSTRAINT_NAME, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN

IF CHARINDEX(@FK_PREFIX, @CONSTRAINT_NAME) = 1 BEGIN
PRINT '

/****** Object: Index [IX_' + @CONSTRAINT_NAME + '] Script Date: ' + CONVERT(NVARCHAR(50), GetDate()) + ' ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[' + @TABLE_SCHEMA + '].[' + @TABLE_NAME + ']'') AND name = N''IX_' + @CONSTRAINT_NAME + ''')
DROP INDEX [IX_' + @CONSTRAINT_NAME + '] ON [' + @TABLE_SCHEMA + '].[' + @TABLE_NAME + '] WITH ( ONLINE = OFF )
GO

CREATE NONCLUSTERED INDEX [IX_' + @CONSTRAINT_NAME + '] ON [' + @TABLE_SCHEMA + '].[' + @TABLE_NAME + ']
(
[' + @COLUMN_NAME + '] 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]
GO

'

END
END
FETCH NEXT FROM FK_INDEX_CURSOR INTO @CONSTRAINT_NAME, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME
END

CLOSE FK_INDEX_CURSOR
DEALLOCATE FK_INDEX_CURSOR

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: