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.)
129 Answers
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:
13If 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.
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 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.
2Here'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.
1The 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 to play with your results
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
1 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 -
2I 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; 
