I have table prsl which have auto generated name of the constraint. I want to search where the Database kept these name. ALTER TABLE [dbo].[PRSL] DROP CONSTRAINT [PK__PRSL__1C1D47DC0BF1ACC7]
Actually, i want to drop these constraints dynamically. For Example
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[PRSL]') drop all the constraint which are on a table.
Drop constraint 'when found'
If you're willing to display all constraints of a given table
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME = 'YOUR TABLE NAME' If you want to drop all constraints of the given table use this:
DECLARE @database nvarchar(50) DECLARE @table nvarchar(50) set @database = 'dotnetnuke' set @table = 'tabs' DECLARE @sql nvarchar(255) WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table) BEGIN select @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table exec sp_executesql @sql END It worked for me...Hope it helps...