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

Identify the duplicates in MySQL

To identify the duplicate entries in MySQL, use below query.                      SELECT COUNT(*) as repetitions, group_concat(id, ' (', startTime, ', ', endTime, ') ' SEPARATOR ' | ') as row_data FROM GROUP BY startTime, endTime HAVING repetitions > 1 FYI, id, startTime and endTime columns should be changed based on your table schema. you can change the output format here like alias and the separator.

Exclude or Include table(s) during creating backup from MySQL

 To exclude tables when you are creating database backup from MySQL, execute below query. mysqldump -u root -p<Password> DATABASENAME --ignore-table= DATABASENAME.TABLENAME  > BACKUPFILENAME.sql  To include  tables when you are creating database backup from MySQL, execute below query. mysqldump -u root -p<Password> DATABASENAME TABLENAME1 TABLENAME2  > BACKUPFILENAME.sql Take note that there is no whitespace between -p and the password..