UNIQUE KEY INDEX AND PRIMARY KEY DROP/CREATE GENERATOR


/*
UNIQUE KEY INDEX AND PRIMARY KEY DROP/CREATE GENERATOR

THIS SCRIPT GENERATES SQL TO DROPS AND RECREATE ALL *PREVIOUSLY DEFINED* UNIQUE INDEXES ON ALL TABLES
SET @FK_PREFIX TO MATCH THE FOREIGN KEY PREFIX IN YOUR DATABASE.

SQL2005 AND ABOVE ONLY (SYS VIEWS)

----------- IMPORTANT CONFIG SETTINGS: ---------------------

SET CONSTRAINT_TYPE:
'UNIQUE'
'PRIMARY KEY'

SET GENERATE_MODE:
1 = DROP
2 = CREATE
3 = RECREATE

------------------------------------------------------------
*/
DECLARE @GENERATE_MODE INT
SET @GENERATE_MODE = 3
DECLARE @CONSTRAINT_TYPE NVARCHAR(25)
SET @CONSTRAINT_TYPE = 'PRIMARY KEY'

DECLARE @CREATE_AS NVARCHAR(25)
SET @CREATE_AS = 'UNIQUE NONCLUSTERED'
IF @CONSTRAINT_TYPE = 'PRIMARY KEY' BEGIN
SET @CREATE_AS = 'PRIMARY KEY CLUSTERED'
END

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

DECLARE FK_INDEX_CURSOR CURSOR READ_ONLY
FOR SELECT
CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = @CONSTRAINT_TYPE
AND TABLE_NAME NOT LIKE 'sys%'

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

SET @COLS = ''
SELECT @COLS = @COLS + '[' + COLUMN_NAME + '] ASC,'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME = @CONSTRAINT_NAME
ORDER BY COLUMN_NAME DESC

IF @GENERATE_MODE = 1 OR @GENERATE_MODE = 3 BEGIN
PRINT '
/****** Object: Index [' + @CONSTRAINT_NAME + '] Script Date: ' + CONVERT(NVARCHAR(50), GetDate()) + ' ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].[' + @TABLE_NAME + ']'') AND name = N''' + @CONSTRAINT_NAME + ''')
ALTER TABLE [dbo].[' + @TABLE_NAME + '] DROP CONSTRAINT [' + @CONSTRAINT_NAME + ']
GO
'
END

IF @GENERATE_MODE = 2 OR @GENERATE_MODE = 3 BEGIN
IF @GENERATE_MODE = 2 BEGIN
PRINT '
/****** Object: Index [IX_' + @CONSTRAINT_NAME + '] Script Date: ' + CONVERT(NVARCHAR(50), GetDate()) + ' ******/'
END
PRINT ' ALTER TABLE [dbo].[' + @TABLE_NAME + '] ADD CONSTRAINT [' + @CONSTRAINT_NAME + '] ' + @CREATE_AS + '
(
' +
SUBSTRING(@COLS, 1, LEN(@COLS)-1)
+ '

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = 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
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: