Technical Article

Create/ drop unique key

,

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

Rate

1.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

1.5 (2)

You rated this post out of 5. Change rating