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