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

I need to remove a highly referenced table in a SQL Server database. How can I get a list of all the foreign key constraints I will need to remove in order to drop the table?

(SQL answers preferable over clicking about in the GUI of the management studio.)

Not sure why no one suggested but I use sp_fkeys to query foreign keys for a given table:

EXEC sp_fkeys 'TableName'

You can also specify the schema:

EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'

Without specifying the schema, the docs state the following:

> If pktable_owner is not specified, the default table visibility rules > of the underlying DBMS apply. > > In SQL Server, if the current user owns a table with the specified > name, that table's columns are returned. If pktable_owner is not > specified and the current user does not own a table with the specified > pktable_name, the procedure looks for a table with the specified > pktable_name owned by the database owner. If one exists, that table's > columns are returned.

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')
)

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

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 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.

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'

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')

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

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...

First

EXEC sp_fkeys 'Table', 'Schema'

Then use [NimbleText][1] to play with your results

[1]: http://nimbletext.com/ "Nimble Text"

 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.

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(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

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]' 

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

with tab_list as (
	select t.name AS Table_Name, t.object_id, s.name AS Table_Schema  from sys.tables t, sys.schemas s 
	 where t.schema_id = s.schema_id
	   and s.name = 'your schema') 
select IIF(col.column_id = 1, tab.TABLE_SCHEMA + '.' + tab.TABLE_NAME, NULL) Table_Name,
	   col.Name AS Column_Name, IIF(col.IS_NULLABLE= 0, 'NOT NULL', '') Nullable, st.name Type,
       CASE WHEN st.name = 'decimal' THEN CONVERT(NVARCHAR(4000), col.Precision) + ',' + CONVERT(NVARCHAR(4000), col.Scale) 
		    WHEN col.max_length = -1 THEN 'max'
			WHEN st.name in ('int', 'bit', 'bigint', 'datetime2') THEN NULL
	   ELSE CONVERT(NVARCHAR(4000), col.max_length / 2)
	   END
	   AS Length,
       ss.name + '.' + stab.name Referenced_Table, scol.name Referenced_Column 
from sys.COLUMNS col  
	INNER JOIN tab_list tab ON col.object_id = tab.object_id
	INNER JOIN sys.types st ON col.system_type_id = st.system_type_id AND col.user_type_id = st.user_type_id 
	LEFT JOIN [sys].[foreign_key_columns] sfkc ON col.object_id = sfkc.parent_object_id AND col.column_id = sfkc.parent_column_id
	LEFT JOIN sys.tables stab ON sfkc.referenced_object_id = stab.object_id
	LEFT JOIN sys.columns scol ON sfkc.referenced_object_id = scol.object_id AND sfkc.referenced_column_id = scol.column_id 
	LEFT JOIN sys.schemas ss ON ss.schema_id = stab.schema_id

Oracle SQL

select *
from
    all_constraints
where
    r_constraint_name in
    (select       constraint_name
    from
       all_constraints
    where
       table_name='PUT_THE_TABLE_NAME_HERE');

all_constraints is an intrinsic table name in Oracle DB.

This answer builds on but is formatted like sp_fkeys, works on multiple columns and lists their order.

SELECT fk_obj.name    AS FK_NAME,
       pk_schema.name AS PKTABLE_OWNER,
       pk_table.name  AS PKTABLE_NAME,
       pk_column.name AS PKCOLUMN_NAME,
       fk_schema.name AS FKTABLE_OWNER,
       fk_table.name  AS FKTABLE_NAME,
       fk_column.name AS FKCOLUMN_NAME,
       ROW_NUMBER() over (
           PARTITION BY fk_obj.name, fk_schema.name
           ORDER BY fkc.constraint_column_id
           )          AS KEY_SEQ
FROM sys.foreign_key_columns fkc
         INNER JOIN sys.objects fk_obj
                    ON fk_obj.object_id = fkc.constraint_object_id
         INNER JOIN sys.tables fk_table
                    ON fk_table.object_id = fkc.parent_object_id
         INNER JOIN sys.schemas fk_schema
                    ON fk_table.schema_id = fk_schema.schema_id
         INNER JOIN sys.columns fk_column
                    ON fk_column.column_id = parent_column_id
                        AND fk_column.object_id = fk_table.object_id
         INNER JOIN sys.tables pk_table
                    ON pk_table.object_id = fkc.referenced_object_id
         INNER JOIN sys.schemas pk_schema
                    ON pk_table.schema_id = pk_schema.schema_id
         INNER JOIN sys.columns pk_column
                    ON pk_column.column_id = fkc.referenced_column_id
                        AND pk_column.object_id = pk_table.object_id;