How can I list all foreign keys referencing a given table in SQL Server?

This gives you:

• The FK itself itself
• Schema that the FK belongs to
• The "referencing table" or the table that has the FK
• The "referencing column" or the column inside referencing table that points to the FK
• The "referenced table" or the table that has the key column that your FK is pointing to
• The "referenced column" or the column that is the key that your FK is pointing to

Code below:

SELECT obj.name AS FK_NAME, sch.name AS [schema_name], tab1.name AS [table], col1.name AS [column], tab2.name AS [referenced_table], col2.name AS [referenced_column] FROM sys.foreign_key_columns fkc INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id INNER JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id 

I'd use the Database Diagramming feature in SQL Server Management Studio, but since you ruled that out - this worked for me in SQL Server 2008 (don't have 2005).

To get list of referring table and column names...

select t.name as TableWithForeignKey, fk.constraint_column_id as FK_PartNo, c. name as ForeignKeyColumn from sys.foreign_key_columns as fk inner join sys.tables as t on fk.parent_object_id = t.object_id inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id where fk.referenced_object_id = (select object_id from sys.tables where name = 'TableOthersForeignKeyInto') order by TableWithForeignKey, FK_PartNo 

To get names of foreign key constraints

select distinct name from sys.objects where object_id in ( select fk.constraint_object_id from sys.foreign_key_columns as fk where fk.referenced_object_id = (select object_id from sys.tables where name = 'TableOthersForeignKeyInto') ) 

Try this :

sp_help 'TableName' 

You should also mind the references to other objects.

If the table was highly referenced by other tables than it’s probably also highly referenced by other objects such as views, stored procedures, functions and more.

I’d really recommend GUI tool such as ‘view dependencies’ dialog in SSMS or free tool like ApexSQL Search for this because searching for dependencies in other objects can be error prone if you want to do it only with SQL.

If SQL is the only option you could try doing it like this.

select O.name as [Object_Name], C.text as [Object_Definition] from sys.syscomments C inner join sys.all_objects O ON C.id = O.object_id where C.text like '%table_name%' 

The most Simplest one is by using sys.foreign_keys_columns in SQL. Here the table contains the Object ids of all the foreign keys wrt their Referenced column ID Referenced Table ID as well as the Referencing Columns and Tables. As the Id's remains constant the result will be reliable for further modifications in Schema as well as tables.

Query:

SELECT OBJECT_NAME(fkeys.constraint_object_id) foreign_key_name ,OBJECT_NAME(fkeys.parent_object_id) referencing_table_name ,COL_NAME(fkeys.parent_object_id, fkeys.parent_column_id) referencing_column_name ,OBJECT_SCHEMA_NAME(fkeys.parent_object_id) referencing_schema_name ,OBJECT_NAME (fkeys.referenced_object_id) referenced_table_name ,COL_NAME(fkeys.referenced_object_id, fkeys.referenced_column_id) referenced_column_name ,OBJECT_SCHEMA_NAME(fkeys.referenced_object_id) referenced_schema_name FROM sys.foreign_key_columns AS fkeys 

We can also add filter by using 'where'

WHERE OBJECT_NAME(fkeys.parent_object_id) = 'table_name' AND OBJECT_SCHEMA_NAME(fkeys.parent_object_id) = 'schema_name' 

The original question asked to get a list of all foreign keys into a highly referenced table so that the table can be removed.

This little query returns all the 'drop foreign key' commands needed to drop all foreign keys into a particular table:

SELECT 'ALTER TABLE ['+sch.name+'].['+referencingTable.Name+'] DROP CONSTRAINT ['+foreignKey.name+']' '[DropCommand]' FROM sys.foreign_key_columns fk JOIN sys.tables referencingTable ON fk.parent_object_id = referencingTable.object_id JOIN sys.schemas sch ON referencingTable.schema_id = sch.schema_id JOIN sys.objects foreignKey ON foreignKey.object_id = fk.constraint_object_id JOIN sys.tables referencedTable ON fk.referenced_object_id = referencedTable.object_id WHERE referencedTable.name = 'MyTableName' 

Example output:

[DropCommand] ALTER TABLE [dbo].[OtherTable1] DROP CONSTRAINT [FK_OtherTable1_MyTable] ALTER TABLE [dbo].[OtherTable2] DROP CONSTRAINT [FK_OtherTable2_MyTable] 

Omit the WHERE-clause to get the drop commands for all foreign keys in the current database.

Here's the SQL code I would use.

SELECT f.name AS 'Name of Foreign Key', OBJECT_NAME(f.parent_object_id) AS 'Table name', COL_NAME(fc.parent_object_id,fc.parent_column_id) AS 'Fieldname', OBJECT_NAME(t.object_id) AS 'References Table name', COL_NAME(t.object_id,fc.referenced_column_id) AS 'References fieldname', 'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id) + '] DROP CONSTRAINT [' + f.name + ']' AS 'Delete foreign key', 'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id) + '] WITH NOCHECK ADD CONSTRAINT [' + f.name + '] FOREIGN KEY([' + COL_NAME(fc.parent_object_id,fc.parent_column_id) + ']) REFERENCES ' + '[' + OBJECT_NAME(t.object_id) + '] ([' + COL_NAME(t.object_id,fc.referenced_column_id) + '])' AS 'Create foreign key' -- , delete_referential_action_desc AS 'UsesCascadeDelete' FROM sys.foreign_keys AS f, sys.foreign_key_columns AS fc, sys.tables t WHERE f.OBJECT_ID = fc.constraint_object_id AND t.OBJECT_ID = fc.referenced_object_id AND OBJECT_NAME(t.object_id) = 'Employees' -- Just show the FKs which reference a particular table ORDER BY 2 

It's not particularly clear SQL, so let's look at an example.

So, supposing I wanted to drop the Employees table in Microsoft's beloved Northwind database, but SQL Server told me that one or more Foreign Keys were preventing me from doing this.

The SQL command above would return these results...

It shows me that there are 3 Foreign Keys which reference the Employees table. In other words, I wouldn't be allowed to delete (drop) this table until these three Foreign Keys are first deleted.

In the results, the first row is how the following Foreign Key constraint would be shown in the results.

ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo]) REFERENCES [dbo].[Employees] ([EmployeeID]) 

The second-to-last column shows the SQL command I would need to use to delete one of these Foreign Keys, eg:

ALTER TABLE [Employees] DROP CONSTRAINT [FK_Employees_Employees] 

...and the right-hand column shows the SQL to create it...

ALTER TABLE [Employees] WITH NOCHECK ADD CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo]) REFERENCES [Employees] ([EmployeeID]) 

With all of these commands, you have everything you need to delete the relevant Foreign Keys to allow you to delete a table, then recreate them later.

Phew. Hope this helps.

SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()), PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)), PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME), PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME), FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()), FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)), FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME), FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME), -- Force the column to be non-nullable (see SQL BU 325751) --KEY_SEQ = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)), UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade') WHEN 1 THEN 0 ELSE 1 END), DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade') WHEN 1 THEN 0 ELSE 1 END), FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)), PK_NAME = CONVERT(SYSNAME,I.NAME), DEFERRABILITY = CONVERT(SMALLINT,7) -- SQL_NOT_DEFERRABLE FROM SYS.ALL_OBJECTS O1, SYS.ALL_OBJECTS O2, SYS.ALL_COLUMNS C1, SYS.ALL_COLUMNS C2, SYS.FOREIGN_KEYS F INNER JOIN SYS.FOREIGN_KEY_COLUMNS K ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID) INNER JOIN SYS.INDEXES I ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID AND F.KEY_INDEX_ID = I.INDEX_ID) WHERE O1.OBJECT_ID = F.REFERENCED_OBJECT_ID AND O2.OBJECT_ID = F.PARENT_OBJECT_ID AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID AND C2.OBJECT_ID = F.PARENT_OBJECT_ID AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID AND C2.COLUMN_ID = K.PARENT_COLUMN_ID 

SELECT object_name(parent_object_id), object_name(referenced_object_id), name FROM sys.foreign_keys WHERE parent_object_id = object_id('Table Name') 

List of all foreign keys referencing a given table in SQL Server :

You can get the referencing table name and column name through following query...

SELECT OBJECT_NAME(f.parent_object_id) TableName, COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id WHERE OBJECT_NAME (f.referenced_object_id) = 'TableName' 

And following screenshot for your understanding...

I am using this script to find all details related to foreign key. I am using INFORMATION.SCHEMA. Below is a SQL Script:

SELECT ccu.table_name AS SourceTable ,ccu.constraint_name AS SourceConstraint ,ccu.column_name AS SourceColumn ,kcu.table_name AS TargetTable ,kcu.column_name AS TargetColumn FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON kcu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME ORDER BY ccu.table_name 

First

EXEC sp_fkeys 'Table', 'Schema' 

Then use NimbleText to play with your results

I know that its a late(very late) reply, but I find these easy ways to find all the foreign_key_references. Here're the solutions;

Solution 01:

EXEC SP_FKEYS 'MyTableName'; // It'll show you the all the information(in multiple tables) regarding to the TableName with all ForeignKey_References. 

Solution 02:

EXEC SP_HELP 'MyTableName'; // It'll show all ForeignKey references in a single table. 

Solution 03:

// It'll show you the Column_Name with Referenced_Table_Name SELECT COL_NAME(fc.parent_object_id,fc.parent_column_id) Column_Name, OBJECT_NAME(f.parent_object_id) Table_Name FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id WHERE OBJECT_NAME (f.referenced_object_id) = 'MyTableName' 

Hopefully, this'll help you a lot. ;-)

Some good answers above. But I prefer to have the answer with one query. This piece of code is taken from sys.sp_helpconstraint (sys proc)

That's the way Microsoft looks up if there are foreign keys associated to the tbl.

--setup variables. Just change 'Customer' to tbl you want declare @objid int, @objname nvarchar(776) select @objname = 'Customer' select @objid = object_id(@objname) if exists (select * from sys.foreign_keys where referenced_object_id = @objid) select 'Table is referenced by foreign key' = db_name() + '.' + rtrim(schema_name(ObjectProperty(parent_object_id,'schemaid'))) + '.' + object_name(parent_object_id) + ': ' + object_name(object_id) from sys.foreign_keys where referenced_object_id = @objid order by 1 

The answer will look like this: test_db_name.dbo.Account: FK_Account_Customer

 SELECT OBJECT_NAME(fk.parent_object_id) as ReferencingTable, OBJECT_NAME(fk.constraint_object_id) as [FKContraint] FROM sys.foreign_key_columns as fk WHERE fk.referenced_object_id = OBJECT_ID('ReferencedTable', 'U') 

This only shows the relationship if the are foreign key constraints. My database apparently predates the FK constraint.Some table use triggers to enforce referential integrity, and sometimes there's nothing but a similarly named column to indicate the relationship (and no referential integrity at all).

Fortunately, we do have a consistent naming scene so I am able to find referencing tables and views like this:

SELECT OBJECT_NAME(object_id) from sys.columns where name like 'client_id' 

I used this select as the basis for generating a script the does what I need to do on the related tables.

SELECT OBJECT_NAME(parent_object_id) 'Parent table', c.NAME 'Parent column name', OBJECT_NAME(referenced_object_id) 'Referenced table', cref.NAME 'Referenced column name' FROM sys.foreign_key_columns fkc INNER JOIN sys.columns c ON fkc.parent_column_id = c.column_id AND fkc.parent_object_id = c.object_id INNER JOIN sys.columns cref ON fkc.referenced_column_id = cref.column_id AND fkc.referenced_object_id = cref.object_id where OBJECT_NAME(parent_object_id) = 'tablename' 

If you want to get the foreign key relation of all the tables exclude the where clause else write your tablename instead of tablename

Most preferable answer by @BankZ

sp_help 'TableName' 

additionally for different schema

sp_help 'schemaName.TableName' 

Mysql server has information_schema.REFERENTIAL_CONSTRAINTS table FYI, you can filter it by table name or referenced table name.

Working off of what @Gishu did I was able to produce and use the following SQL in SQL Server 2005

SELECT t.name AS TableWithForeignKey, fk.constraint_column_id AS FK_PartNo, c.name AS ForeignKeyColumn, o.name AS FK_Name FROM sys.foreign_key_columns AS fk INNER JOIN sys.tables AS t ON fk.parent_object_id = t.object_id INNER JOIN sys.columns AS c ON fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id INNER JOIN sys.objects AS o ON fk.constraint_object_id = o.object_id WHERE fk.referenced_object_id = (SELECT object_id FROM sys.tables WHERE name = 'TableOthersForeignKeyInto') ORDER BY TableWithForeignKey, FK_PartNo; 

Which Displays the tables, columns and Foreign Key names all in 1 query.

Determine primary keys and unique keys for all tables in a database...

This should list all the constraints and at the end you can put your filters

/* CAST IS DONE , SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT*/ WITH ALL_KEYS_IN_TABLE (CONSTRAINT_NAME,CONSTRAINT_TYPE,PARENT_TABLE_NAME,PARENT_COL_NAME,PARENT_COL_NAME_DATA_TYPE,REFERENCE_TABLE_NAME,REFERENCE_COL_NAME) AS ( SELECT CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)) , CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)) , PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)) , PARENT_COL_NAME=CAST ( PKnUKEYCol.name AS VARCHAR(30)) , PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE, REFERENCE_TABLE_NAME='' , REFERENCE_COL_NAME='' FROM sys.key_constraints as PKnUKEY INNER JOIN sys.tables as PKnUTable ON PKnUTable.object_id = PKnUKEY.parent_object_id INNER JOIN sys.index_columns as PKnUColIdx ON PKnUColIdx.object_id = PKnUTable.object_id AND PKnUColIdx.index_id = PKnUKEY.unique_index_id INNER JOIN sys.columns as PKnUKEYCol ON PKnUKEYCol.object_id = PKnUTable.object_id AND PKnUKEYCol.column_id = PKnUColIdx.column_id INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl ON oParentColDtl.TABLE_NAME=PKnUTable.name AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name UNION ALL SELECT CONSTRAINT_NAME= CAST (oConstraint.name AS VARCHAR(30)) , CONSTRAINT_TYPE='FK', PARENT_TABLE_NAME=CAST (oParent.name AS VARCHAR(30)) , PARENT_COL_NAME=CAST ( oParentCol.name AS VARCHAR(30)) , PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE, REFERENCE_TABLE_NAME=CAST ( oReference.name AS VARCHAR(30)) , REFERENCE_COL_NAME=CAST (oReferenceCol.name AS VARCHAR(30)) FROM sys.foreign_key_columns FKC INNER JOIN sys.sysobjects oConstraint ON FKC.constraint_object_id=oConstraint.id INNER JOIN sys.sysobjects oParent ON FKC.parent_object_id=oParent.id INNER JOIN sys.all_columns oParentCol ON FKC.parent_object_id=oParentCol.object_id /* ID of the object to which this column belongs.*/ AND FKC.parent_column_id=oParentCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/ INNER JOIN sys.sysobjects oReference ON FKC.referenced_object_id=oReference.id INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl ON oParentColDtl.TABLE_NAME=oParent.name AND oParentColDtl.COLUMN_NAME=oParentCol.name INNER JOIN sys.all_columns oReferenceCol ON FKC.referenced_object_id=oReferenceCol.object_id /* ID of the object to which this column belongs.*/ AND FKC.referenced_column_id=oReferenceCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/ ) select * from ALL_KEYS_IN_TABLE where PARENT_TABLE_NAME in ('YOUR_TABLE_NAME') or REFERENCE_TABLE_NAME in ('YOUR_TABLE_NAME') ORDER BY PARENT_TABLE_NAME,CONSTRAINT_NAME; 

For reference please read thru - http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx

I have been using this on 2008 and up. It's similar to some other solutions listed but, the field names are proper cased to handle case specific (LatBin) collations. Additionally, you can feed it a single table name and retrieve just the info for that table.

-->>SPECIFY THE DESIRED DB USE ??? GO /********************************************************************************************* LIST OUT ALL PRIMARY AND FOREIGN KEY CONSTRAINTS IN A DB OR FOR A SPECIFIED TABLE *********************************************************************************************/ DECLARE @tblName VARCHAR(255) /*******************/ SET @tblName = NULL-->NULL will return all PK/FK constraints for every table in the database /*******************/ SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()), PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.schema_id)), PKTABLE_NAME = CONVERT(SYSNAME,O1.name), PKCOLUMN_NAME = CONVERT(SYSNAME,C1.name), FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()), FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.schema_id)), FKTABLE_NAME = CONVERT(SYSNAME,O2.name), FKCOLUMN_NAME = CONVERT(SYSNAME,C2.name), -- Force the column to be non-nullable (see SQL BU 325751) KEY_SEQ = isnull(convert(smallint,K.constraint_column_id),0), UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.object_id,'CnstIsUpdateCascade') WHEN 1 THEN 0 ELSE 1 END), DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.object_id,'CnstIsDeleteCascade') WHEN 1 THEN 0 ELSE 1 END), FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.object_id)), PK_NAME = CONVERT(SYSNAME,I.name), DEFERRABILITY = CONVERT(SMALLINT,7) -- SQL_NOT_DEFERRABLE FROM sys.all_objects O1, sys.all_objects O2, sys.all_columns C1, sys.all_columns C2, sys.foreign_keys F INNER JOIN sys.foreign_key_columns K ON (K.constraint_object_id = F.object_id) INNER JOIN sys.indexes I ON (F.referenced_object_id = I.object_id AND F.key_index_id = I.index_id) WHERE O1.object_id = F.referenced_object_id AND O2.object_id = F.parent_object_id AND C1.object_id = F.referenced_object_id AND C2.object_id = F.parent_object_id AND C1.column_id = K.referenced_column_id AND C2.column_id = K.parent_column_id AND ( O1.name = @tblName OR O2.name = @tblName OR @tblName IS null) ORDER BY PKTABLE_NAME,FKTABLE_NAME 

There is how to get count of all responsibilities for selected Id. Just change @dbTableName value, @dbRowId value and its type (if int you need to remove '' in line no 82 (..SET @SQL = ..)). Enjoy.

DECLARE @dbTableName varchar(max) = 'User' DECLARE @dbRowId uniqueidentifier = '21d34ecd-c1fd-11e2-8545-002219a42e1c' DECLARE @FK_ROWCOUNT int DECLARE @SQL nvarchar(max) DECLARE @PKTABLE_QUALIFIER sysname DECLARE @PKTABLE_OWNER sysname DECLARE @PKTABLE_NAME sysname DECLARE @PKCOLUMN_NAME sysname DECLARE @FKTABLE_QUALIFIER sysname DECLARE @FKTABLE_OWNER sysname DECLARE @FKTABLE_NAME sysname DECLARE @FKCOLUMN_NAME sysname DECLARE @UPDATE_RULE smallint DECLARE @DELETE_RULE smallint DECLARE @FK_NAME sysname DECLARE @PK_NAME sysname DECLARE @DEFERRABILITY sysname IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL DROP TABLE #Temp1; CREATE TABLE #Temp1 ( PKTABLE_QUALIFIER sysname, PKTABLE_OWNER sysname, PKTABLE_NAME sysname, PKCOLUMN_NAME sysname, FKTABLE_QUALIFIER sysname, FKTABLE_OWNER sysname, FKTABLE_NAME sysname, FKCOLUMN_NAME sysname, UPDATE_RULE smallint, DELETE_RULE smallint, FK_NAME sysname, PK_NAME sysname, DEFERRABILITY sysname, FK_ROWCOUNT int ); DECLARE FK_Counter_Cursor CURSOR FOR SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()), PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)), PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME), PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME), FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()), FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)), FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME), FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME), -- Force the column to be non-nullable (see SQL BU 325751) --KEY_SEQ = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)), UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade') WHEN 1 THEN 0 ELSE 1 END), DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade') WHEN 1 THEN 0 ELSE 1 END), FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)), PK_NAME = CONVERT(SYSNAME,I.NAME), DEFERRABILITY = CONVERT(SMALLINT,7) -- SQL_NOT_DEFERRABLE FROM SYS.ALL_OBJECTS O1, SYS.ALL_OBJECTS O2, SYS.ALL_COLUMNS C1, SYS.ALL_COLUMNS C2, SYS.FOREIGN_KEYS F INNER JOIN SYS.FOREIGN_KEY_COLUMNS K ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID) INNER JOIN SYS.INDEXES I ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID AND F.KEY_INDEX_ID = I.INDEX_ID) WHERE O1.OBJECT_ID = F.REFERENCED_OBJECT_ID AND O2.OBJECT_ID = F.PARENT_OBJECT_ID AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID AND C2.OBJECT_ID = F.PARENT_OBJECT_ID AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID AND C2.COLUMN_ID = K.PARENT_COLUMN_ID AND O1.NAME = @dbTableName OPEN FK_Counter_Cursor; FETCH NEXT FROM FK_Counter_Cursor INTO @PKTABLE_QUALIFIER, @PKTABLE_OWNER, @PKTABLE_NAME, @PKCOLUMN_NAME, @FKTABLE_QUALIFIER, @FKTABLE_OWNER, @FKTABLE_NAME, @FKCOLUMN_NAME, @UPDATE_RULE, @DELETE_RULE, @FK_NAME, @PK_NAME, @DEFERRABILITY; WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'SELECT @dbCountOut = COUNT(*) FROM [' + @FKTABLE_NAME + '] WHERE [' + @FKCOLUMN_NAME + '] = ''' + CAST(@dbRowId AS varchar(max)) + ''''; EXECUTE sp_executesql @SQL, N'@dbCountOut int OUTPUT', @dbCountOut = @FK_ROWCOUNT OUTPUT; INSERT INTO #Temp1 (PKTABLE_QUALIFIER, PKTABLE_OWNER, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_QUALIFIER, FKTABLE_OWNER, FKTABLE_NAME, FKCOLUMN_NAME, UPDATE_RULE, DELETE_RULE, FK_NAME, PK_NAME, DEFERRABILITY, FK_ROWCOUNT) VALUES (@FKTABLE_QUALIFIER, @PKTABLE_OWNER, @PKTABLE_NAME, @PKCOLUMN_NAME, @FKTABLE_QUALIFIER, @FKTABLE_OWNER, @FKTABLE_NAME, @FKCOLUMN_NAME, @UPDATE_RULE, @DELETE_RULE, @FK_NAME, @PK_NAME, @DEFERRABILITY, @FK_ROWCOUNT) FETCH NEXT FROM FK_Counter_Cursor INTO @PKTABLE_QUALIFIER, @PKTABLE_OWNER, @PKTABLE_NAME, @PKCOLUMN_NAME, @FKTABLE_QUALIFIER, @FKTABLE_OWNER, @FKTABLE_NAME, @FKCOLUMN_NAME, @UPDATE_RULE, @DELETE_RULE, @FK_NAME, @PK_NAME, @DEFERRABILITY; END; CLOSE FK_Counter_Cursor; DEALLOCATE FK_Counter_Cursor; GO SELECT * FROM #Temp1 GO 

The following solution work for me:

--Eliminar las llaves foraneas declare @query varchar(8000) declare cursorRecorrerTabla cursor for SELECT 'ALTER TABLE [PoaComFinH].['+sch.name+'].['+referencingTable.Name+'] DROP CONSTRAINT ['+foreignKey.name+']' 'query' FROM PoaComFinH.sys.foreign_key_columns fk JOIN PoaComFinH.sys.tables referencingTable ON fk.parent_object_id = referencingTable.object_id JOIN PoaComFinH.sys.schemas sch ON referencingTable.schema_id = sch.schema_id JOIN PoaComFinH.sys.objects foreignKey ON foreignKey.object_id = fk.constraint_object_id JOIN PoaComFinH.sys.tables referencedTable ON fk.referenced_object_id = referencedTable.object_id --3ro. abrir el cursor. open cursorRecorrerTabla fetch next from cursorRecorrerTabla into @query while @@fetch_status = 0 begin --inicio cuerpo del cursor print @query exec(@query) --fin cuerpo del cursor fetch next from cursorRecorrerTabla into @query end --cerrar cursor close cursorRecorrerTabla deallocate cursorRecorrerTabla 

You can find through below query :

 SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table', OBJECT_NAME(FK.parent_object_id) 'Referring Table', FK.name 'Foreign Key', COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column', COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column' FROM sys.foreign_keys AS FK INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.OBJECT_ID WHERE OBJECT_NAME (FK.referenced_object_id) = 'YourTableName' AND COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) = 'YourColumnName' order by OBJECT_NAME(FK.parent_object_id) 

Also try.

EXEC sp_fkeys 'tableName', 'schemaName' 

with sp_fkeys you may filter the result by not only pk table name and schema but also with fk table name and schema. link

Here is the best practice in my opinion to work over this scenario in SQL Server 2016.

You have to list the foreign keys using :

EXEC sp_fkeys 'TableName' 

There you can see the full info of the FKs. Notice the columns FKTABLE_NAME, FKCOLUMN_NAME, FK_NAME, UPDATE_RULE, DELETE_RULE is the info you need to remove foreign keys and implement them again after truncate.

You can organise a script as follows:

-- EXEC sp_fkeys 'TableName' -- DROP CONSTRAINTS: I drop one, here drop every constraint you desire. BEGIN TRANSACTION GO ALTER TABLE dbo.TableName DROP CONSTRAINT IF EXISTS FK_TableName_OtherTable GO ALTER TABLE dbo.TableName SET (LOCK_ESCALATION = TABLE) GO COMMIT -- TRUNCATE BEGIN TRANSACTION TRUNCATE TABLE TableName GO COMMIT -- RECREATE CONSTRAINTS: I recreate 1, here recreate every fk you desire BEGIN TRANSACTION GO ALTER TABLE dbo.TableName SET (LOCK_ESCALATION = TABLE) GO ALTER TABLE dbo.TableName ADD CONSTRAINT FK_TableName_OtherTable FOREIGN KEY ( Id_FK ) REFERENCES dbo.OtherTable ( Id ) ON UPDATE NO ACTION ON DELETE NO ACTION GO COMMIT 

** Values for UPDATE_RULE and DELETE_RULE can be seen in documentation of sp_fkeys:

Personnally I use this :

declare @schema varchar(500) = 'the_schema'; declare @table varchar(500) = 'the_table'; SELECT obj.name AS FK_NAME, sch1.name + '.' + tab1.name + '.'+ col1.name AS [column1], sch2.name + '.'+ tab2.name + '.' + col2.name AS [column2], typ.name FROM sys.foreign_key_columns fkc INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id INNER JOIN sys.schemas sch1 ON tab1.schema_id = sch1.schema_id INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id INNER JOIN sys.schemas sch2 ON tab2.schema_id = sch2.schema_id INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id INNER JOIN sys.types typ ON col1.user_type_id = typ.user_type_id WHERE (tab1.name = @table and sch1.name = @schema) OR (tab2.name = @table and sch2.name = @schema) ORDER by col2.name 

I like it because it's contained in one query (you can remove the declare and inline them in the query if you don't like them), and it doesn't rely on system procedures so it's highly customizable.

And it will give both :

• The foreign keys in my table
• The foreign keys referencing my table

Which I find convenient. I hope this will help other people reading this topic.

744 / 5 000

Most of the queries provided in these answers probably overlook the fact that a referential integrity constraint (FK) can consist of multiple columns!

Some use uppercase names, while the SQL identifiers (table names, column names, etc.) of system objects are lowercase, which can be problematic in an instance with CASE-SENSITIVE collation.

Others cast object names by limiting them to 30 characters, whereas the standard SQL and SQL Server uses 128 characters.

Some other omits the management clause ON UPDATE/DELETE...

The query below lists all FK constraints for all tables in the current database, along with the list of foreign key columns and the list of reference keys (PK or UNIQUE).

SELECT sfk.name AS FK_TABLE_SCHEMA, fkt.name AS FK_TABLE_NAME, fk.name AS FK_CONSTRAINT_NAME, substring(CFK.column_names, 1, len(CFK.column_names)-1) AS FK_TARGET_COLUMN_LIST, spk.name AS REF_TABLE_SCHEMA, pkt.name AS REF_TABLE_NAME, substring(CPK.column_names, 1, len(CPK.column_names)-1) AS PK_SOURCE_COLUMN_LIST, delete_referential_action_desc AS DELETE_ACTION, update_referential_action_desc AS UPDATE_ACTION FROM sys.foreign_keys fk INNER JOIN sys.tables fkt ON fkt.object_id = fk.parent_object_id INNER JOIN sys.schemas AS sfk ON fkt.schema_id = sfk.schema_id INNER JOIN sys.tables pkt ON pkt.object_id = fk.referenced_object_id INNER JOIN sys.schemas AS spk ON pkt.schema_id = spk.schema_id CROSS APPLY (SELECT QUOTENAME(col.name) + ', ' FROM sys.foreign_key_columns AS fkcol INNER JOIN sys.columns AS col ON fkcol.parent_object_id = col.object_id and fkcol.parent_column_id = col.column_id where fkcol.parent_object_id = fkt.object_id and fkcol.constraint_object_id = fk.object_id order by fkcol.constraint_column_id for xml path ('') ) AS CFK (column_names) CROSS APPLY (select QUOTENAME(col.name) + ', ' from sys.foreign_key_columns pkcol inner join sys.columns col on pkcol.referenced_object_id = col.object_id and pkcol.referenced_column_id = col.column_id where pkcol.referenced_object_id = pkt.object_id and pkcol.constraint_object_id = fk.object_id order by pkcol.constraint_column_id for xml path ('') ) AS CPK (column_names); 

You can test this query with the following tables :

CREATE TABLE A ( CA int NOT NULL, CB int NOT NULL, CC int NOT NULL, PRIMARY KEY (CB, CA, CC) ); CREATE TABLE B ( XX int NOT NULL, YY int NOT NULL, ZZ int NOT NULL, FOREIGN KEY(ZZ, XX, YY) REFERENCES A (CB, CA, CC) ); 

To have the DROP CONSTRAINT and, if you want, the ADD CONSTRAINT for this list, you can use the CTE :

WITH T0 AS ( ) SELECT N'ALTER TABLE ' + QUOTENAME(FK_TABLE_SCHEMA) + N'.' + QUOTENAME(FK_TABLE_NAME) + N' DROP CONSTRAINT ' + QUOTENAME(FK_CONSTRAINT_NAME) + ';' AS DROP_FK, N'ALTER TABLE ' + QUOTENAME(FK_TABLE_SCHEMA) + N'.' + QUOTENAME(FK_TABLE_NAME) + N' ADD CONSTRAINT ' + QUOTENAME(FK_CONSTRAINT_NAME) + N'(' + FK_TARGET_COLUMN_LIST + N')' + N' REFERENCES ' + QUOTENAME(REF_TABLE_SCHEMA) + N'.' + QUOTENAME(REF_TABLE_NAME) + N')' + N' ON UPDATE ' + UPDATE_ACTION COLLATE database_default + N' ON DELETE ' + DELETE_ACTION COLLATE database_default + ';' AS ADD_FK FROM T0; 

And add the firts query between the parenthesis of the CTE...

Another solution is to use the standard ISO SQL views of the INFORMATION_SCHEMA SQL schema and standardized functionnality (no CROSS APPLY as an example) :

WITH T0 AS ( SELECT fkt.TABLE_SCHEMA AS FK_TABLE_SCHEMA, fkt.TABLE_NAME AS FK_TABLE_NAME, fk.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME, pkt.TABLE_SCHEMA AS REF_TABLE_SCHEMA, pkt.TABLE_NAME AS REF_TABLE_NAME, pkt.CONSTRAINT_NAME AS REF_CONSTRAINT_NAME, fk.DELETE_RULE AS DELETE_ACTION, fk.UPDATE_RULE AS UPDATE_ACTION, (SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU WHERE KCU.CONSTRAINT_SCHEMA = fkt.TABLE_SCHEMA AND KCU.CONSTRAINT_NAME = fk.CONSTRAINT_NAME) AS N FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fk INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fkt ON fkt.CONSTRAINT_SCHEMA = fk.CONSTRAINT_SCHEMA AND fkt.CONSTRAINT_NAME = fk.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pkt ON pkt.CONSTRAINT_SCHEMA = fk.UNIQUE_CONSTRAINT_SCHEMA AND pkt.CONSTRAINT_NAME = fk.UNIQUE_CONSTRAINT_NAME ), -- recursive query to list columns of FK FKC AS ( SELECT KCU.CONSTRAINT_SCHEMA , KCU.CONSTRAINT_NAME, KCU.ORDINAL_POSITION, CAST(KCU.COLUMN_NAME AS NVARCHAR(max)) AS FK_COLS FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU JOIN T0 ON KCU.CONSTRAINT_SCHEMA = T0.FK_TABLE_SCHEMA AND KCU.CONSTRAINT_NAME = T0.FK_CONSTRAINT_NAME WHERE ORDINAL_POSITION = 1 UNION ALL SELECT KCU.CONSTRAINT_SCHEMA , KCU.CONSTRAINT_NAME, KCU.ORDINAL_POSITION, FKC.FK_COLS + ', ' + KCU.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU JOIN T0 ON KCU.CONSTRAINT_SCHEMA = T0.FK_TABLE_SCHEMA AND KCU.CONSTRAINT_NAME = T0.FK_CONSTRAINT_NAME JOIN FKC ON FKC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA AND FKC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND FKC.ORDINAL_POSITION + 1 = KCU.ORDINAL_POSITION ), -- recursive query to list columns of PK or UK PKC AS ( SELECT KCU.CONSTRAINT_SCHEMA , KCU.CONSTRAINT_NAME, KCU.ORDINAL_POSITION, CAST(KCU.COLUMN_NAME AS NVARCHAR(max)) AS PK_COLS FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU JOIN T0 ON KCU.CONSTRAINT_SCHEMA = T0.REF_TABLE_SCHEMA AND KCU.CONSTRAINT_NAME = T0.REF_CONSTRAINT_NAME WHERE ORDINAL_POSITION = 1 UNION ALL SELECT KCU.CONSTRAINT_SCHEMA , KCU.CONSTRAINT_NAME, KCU.ORDINAL_POSITION, PKC.PK_COLS + ', ' + KCU.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU JOIN T0 ON KCU.CONSTRAINT_SCHEMA = T0.REF_TABLE_SCHEMA AND KCU.CONSTRAINT_NAME = T0.REF_CONSTRAINT_NAME JOIN PKC ON PKC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA AND PKC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND PKC.ORDINAL_POSITION + 1 = KCU.ORDINAL_POSITION ) SELECT FK_TABLE_SCHEMA, FK_TABLE_NAME, FK_CONSTRAINT_NAME, FK_COLS, REF_TABLE_SCHEMA, REF_TABLE_NAME, PK_COLS, UPDATE_ACTION, DELETE_ACTION FROM T0 JOIN FKC ON T0.FK_TABLE_SCHEMA = FKC.CONSTRAINT_SCHEMA AND T0.FK_CONSTRAINT_NAME = FKC.CONSTRAINT_NAME JOIN PKC ON T0.REF_TABLE_SCHEMA = PKC.CONSTRAINT_SCHEMA AND T0.REF_CONSTRAINT_NAME = PKC.CONSTRAINT_NAME WHERE N = FKC.ORDINAL_POSITION AND N = PKC.ORDINAL_POSITION; 

This gets any foreign key that involves the chosen table. *Assumes a _FIRSTABLENAME_SECONDTABLENAME format.

 declare @tablename as varchar(MAX) SET @tablename = 'yourtablename' SELECT name FROM YOURDATABASE.sys.objects WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT' and (name LIKE '%_' + @tablename + 'empdb_%' or name LIKE '%_' + @tablename ) 

This is a more general form:

 SELECT name FROM YOURDATABASE_PROD.sys.objects WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT' and name LIKE '%' + @tablename + '%' and name NOT LIKE '[a-zA-Z0-9]' + @tablename + '%' and name NOT LIKE '%' + @tablename + '[a-zA-Z0-9]'