CODEWITHSUNDEEP
sql-servert-sql
user3757699
user3757699

Reputation:

T-SQL Drop Constraint on Dynamically Selected Table

I have a situation in which I know a constraint name but do not know the name of a table. I would like to write a script that finds and drops the constraint based on its name, regardless of the table on which it resides. My trouble is that this appears to work only on alter table statements, which assume you know the table name. I can find a constraint using this:

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME='constraint_name'

and would like to use the table info provided there to alter whatever table is in my results and drop the constraint.

Pseudo code of what I want:

alter table (select top 1* TABLE_NAME from 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME='constraint_name') drop constraint 'constraint_name'

Upvotes: 0

Views: 1596

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28930

declare @constraint varchar(255)
declare @tbalename varchar(255)

declare @tbalename=select table_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME=@constraint 

declare @sql nvarchar(max)

set @sql='ALTER TABLE '+@tbalename +'DROP CONSTRAINT '+ @constraint ; 

exec(@SQL)

Upvotes: 1

Related Questions