Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'TableName') AND type = (N'U')) DROP TABLE TableName
- go
- ALTER TABLE [dbo].[TableName]
- WITH CHECK ADD CONSTRAINT [FK_TableName_TableName2] FOREIGN KEY([FK_Name])
- REFERENCES [dbo].[TableName2] ([ID])
- go
- IF EXISTS (SELECT *
- FROM sys.foreign_keys
- WHERE object_id = OBJECT_ID(N'dbo.FK_TableName_TableName2')
- AND parent_object_id = OBJECT_ID(N'dbo.TableName')
- )
- ALTER TABLE [dbo.TableName] DROP CONSTRAINT [FK_TableName_TableName2]
- IF (OBJECT_ID('FK_ConstraintName', 'F') IS NOT NULL)
- BEGIN
- ALTER TABLE dbo.TableName DROP CONSTRAINT FK_ConstraintName
- END
- C = CHECK constraint
- D = DEFAULT (constraint or stand-alone)
- F = FOREIGN KEY constraint
- PK = PRIMARY KEY constraint
- UQ = UNIQUE constraint
- AF = Aggregate function (CLR)
- C = CHECK constraint
- D = DEFAULT (constraint or stand-alone)
- F = FOREIGN KEY constraint
- FN = SQL scalar function
- FS = Assembly (CLR) scalar-function
- FT = Assembly (CLR) table-valued function
- IF = SQL inline table-valued function
- IT = Internal table
- P = SQL Stored Procedure
- PC = Assembly (CLR) stored-procedure
- PG = Plan guide
- PK = PRIMARY KEY constraint
- R = Rule (old-style, stand-alone)
- RF = Replication-filter-procedure
- S = System base table
- SN = Synonym
- SO = Sequence object
- SQ = Service queue
- TA = Assembly (CLR) DML trigger
- TF = SQL table-valued-function
- TR = SQL DML trigger
- TT = Table type
- U = Table (user-defined)
- UQ = UNIQUE constraint
- V = View
- X = Extended stored procedure
- create function fnGetForeignKeyName
- (
- @ParentTableName nvarchar(255),
- @ParentColumnName nvarchar(255),
- @ReferencedTableName nvarchar(255),
- @ReferencedColumnName nvarchar(255)
- )
- returns nvarchar(255)
- as
- begin
- declare @name nvarchar(255)
- select @name = fk.name from sys.foreign_key_columns fc
- join sys.columns pc on pc.column_id = parent_column_id and parent_object_id = pc.object_id
- join sys.columns rc on rc.column_id = referenced_column_id and referenced_object_id = rc.object_id
- join sys.objects po on po.object_id = pc.object_id
- join sys.objects ro on ro.object_id = rc.object_id
- join sys.foreign_keys fk on fk.object_id = fc.constraint_object_id
- where
- po.object_id = object_id(@ParentTableName) and
- ro.object_id = object_id(@ReferencedTableName) and
- pc.name = @ParentColumnName and
- rc.name = @ReferencedColumnName
- return @name
- end
- go
- declare @name nvarchar(255)
- declare @sql nvarchar(4000)
- -- hunt for the constraint name on 'Badges.BadgeReasonTypeId' table refs the 'BadgeReasonTypes.Id'
- select @name = dbo.fnGetForeignKeyName('dbo.Badges', 'BadgeReasonTypeId', 'dbo.BadgeReasonTypes', 'Id')
- -- if we find it, the name will not be null
- if @name is not null
- begin
- set @sql = 'alter table Badges drop constraint ' + replace(@name,']', ']]')
- exec (@sql)
- end
- ALTER TABLE [dbo].[TableName]
- DROP CONSTRAINT FK_TableName_TableName2
- IF (OBJECT_ID('DF_Constraint') IS NOT NULL)
- BEGIN
- ALTER TABLE [dbo].[tableName]
- DROP CONSTRAINT DF_Constraint
- END
- Declare @FKeyRemoveQuery NVarchar(max)
- IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'dbo.TableName'))
- BEGIN
- SELECT @FKeyRemoveQuery='ALTER TABLE dbo.TableName DROP CONSTRAINT [' + LTRIM(RTRIM([name])) + ']'
- FROM sys.foreign_keys
- WHERE parent_object_id = OBJECT_ID(N'dbo.TableName')
- EXECUTE Sp_executesql @FKeyRemoveQuery
- END
- DECLARE @ConstraintName nvarchar(200)
- SELECT
- @ConstraintName = KCU.CONSTRAINT_NAME
- FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
- INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
- ON KCU.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
- AND KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
- AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
- WHERE
- KCU.TABLE_NAME = 'TABLE_NAME' AND
- KCU.COLUMN_NAME = 'TABLE_COLUMN_NAME'
- IF @ConstraintName IS NOT NULL EXEC('alter table TABLE_NAME drop CONSTRAINT ' + @ConstraintName)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement