Rename all Unique Constraints in a SQL Server database
Quick and dirty script:
DECLARE @old sysname, @current sysname, @TABLE sysname, @COLUMN sysname, @this_old sysname DECLARE uq_cursor CURSOR FOR SELECT AllUQs.name AS old_name, object_name(AllUQs.parent_obj) AS table_name, col.name AS column_name FROM dbo.sysobjects AS AllUQs JOIN dbo.sysindexes AS ix ON (ix.id = AllUQs.parent_obj) AND (ix.name = AllUQs.name) JOIN dbo.sysindexkeys AS [KEY] ON ([KEY].id = ix.id) AND ([KEY].indid = ix.indid) JOIN dbo.syscolumns AS col ON (col.id = ix.id) AND (col.colid = [KEY].colid) WHERE AllUQs.xtype = 'UQ' ORDER BY old_name OPEN uq_cursor FETCH NEXT FROM uq_cursor INTO @old, @TABLE, @COLUMN SET @current = 'UQ_' + @TABLE SET @this_old = @old WHILE @@FETCH_STATUS = 0 BEGIN IF (@this_old != @old) BEGIN print @this_old + ' - ' + @current -- EXEC sp_rename @this_old, @current, 'OBJECT' SET @current = 'UQ_' + @TABLE + '_' + @COLUMN SET @this_old = @old END ELSE BEGIN SET @current = @current + '__' + @COLUMN END FETCH NEXT FROM uq_cursor INTO @old, @TABLE, @COLUMN END print @this_old + ' - ' + @current -- EXEC sp_rename @this_old, @current, 'OBJECT' CLOSE uq_cursor DEALLOCATE uq_cursor
This will rename all unique constraints to UQ_TableName_Column1Name_Column2Name
(for a unique constraint on two columns.)
Hope this helps someone save themselves 15mins of SQL…
Man this is great! Thanks!!!
Comment by Dave — August 31, 2011 @ 2:40 am