Skip to main content

Find/drop foreign key using table and column

  1. Find all the foreign keys by executing below query.
    SELECT f.name AS ForeignKey, OBJECT_NAME(f.parent_object_id) AS TableName, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id;
  2. To find/drop a foreign key, use below query.
    declare @table_name nvarchar(256)

    declare @col_name nvarchar(256)

    declare @Command nvarchar(1000)

    set @table_name = N'<tableName>'

    set @col_name = N'<columnName>'

    select @command = 'ALTER TABLE ' + OBJECT_NAME(f.parent_object_id) + ' drop constraint ' + f.name from sys.foreign_keys f, sys.foreign_key_columns fc where f.object_id = fc.constraint_object_id and OBJECT_NAME(f.parent_object_id) = @table_name and COL_NAME(fc.parent_object_id, fc.parent_column_id) = @col_name;

    print @Command

    execute (@Command)

Comments

Popular posts from this blog

Table and its count

Table and its count To view all tables and its count by executing command and immediately, use below commands - MSSQL SELECT      sc. name   + '.' + ta. name   TableName,  SUM (pa. rows ) RowCnt FROM      sys.tables ta INNER   JOIN   sys.partitions pa      ON   pa.OBJECT_ID = ta.OBJECT_ID INNER   JOIN   sys.schemas sc      ON   ta.schema_id = sc.schema_id WHERE   ta.is_ms_shipped = 0  AND   pa.index_id  IN   (1,0) GROUP   BY   sc. name ,ta. name ORDER   BY   SUM (pa. rows )  DESC