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.

T-SQL to set Max memory in MSSQL

 To set max memory for Microsoft SQL server, use the below Transact SQL. USE master EXEC sp_configure ''show advanced options'', 1 RECONFIGURE WITH OVERRIDE GO --To set a maximum memory limit, type the following, pressing Enter after each line: USE master EXEC sp_configure ''max server memory (MB)'', <MaxServerMemory> RECONFIGURE WITH OVERRIDE GO --MaxServerMemory is the value of the physical memory in megabytes (MB) that you want to allocate. --To hide the maximum memory setting, type the following, pressing Enter after each line: USE master EXEC sp_configure ''show advanced options'', 0 RECONFIGURE WITH OVERRIDE GO exit