Hi,
This is my first script and yet it's not a [select 'hello world'] ^^.
It will work even if the unique key contain more than 1 column
Hope it will be usefull.
Regards.
Hi,
This is my first script and yet it's not a [select 'hello world'] ^^.
It will work even if the unique key contain more than 1 column
Hope it will be usefull.
Regards.
CREATE FUNCTION dbo.FN_column_UQ( @uq_name varchar(100) ) RETURNS varchar(500) AS BEGIN --join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE sc on sc.constraint_name = uq.name declare @column varchar (100) declare @R varchar (500) set @R='' DECLARE colmns_list Cursor FOR SELECT column_name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where constraint_name =@uq_name --into @column -- Open the cursor OPEN colmns_list -- Fetch the first row of the cursor and assign its values into variables FETCH NEXT FROM colmns_list INTO @column -- perform action whilst a row was found WHILE @@FETCH_STATUS = 0 BEGIN set @R=@R+@column+',' -- get next row of cursor FETCH NEXT FROM colmns_list INTO @column END -- Close the cursor to release locks CLOSE colmns_list -- Free memory used by cursor DEALLOCATE colmns_list return left (@R, len(@r)-1) END go select 'ALTER TABLE '+so.name+' DROP CONSTRAINT '+uq.name from sysobjects so join sysobjects uq on so.id=uq.parent_obj --join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE sc on sc.constraint_name = uq.name where uq.xtype='uq' --and so.name ='table_name' -- uncomment it to filter on table select 'ALTER TABLE '+so.name+' ADD CONSTRAINT '+uq.name+'UNIQUE NONCLUSTERED ('+dbo.FN_column_UQ(uq.name)+')' from sysobjects so join sysobjects uq on so.id=uq.parent_obj --join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE sc on sc.constraint_name = uq.name where uq.xtype='uq' --and so.name ='table_name' -- uncomment it to filter on table go drop FUNCTION dbo.FN_column_UQ