Come posso elencare tutte le chiavi esterne che fanno riferimento a una data tabella in SQL Server?

Devo rimuovere una tabella altamente referenziata in un database di SQL Server. Come posso ottenere un elenco di tutti i vincoli delle chiavi esterne che dovrò rimuovere per far cadere il tavolo?

(Risposte SQL preferibili facendo clic su nella GUI dello studio di gestione.)

Non sono sicuro del motivo per cui nessuno ha suggerito, ma io uso sp_fkeys per interrogare le chiavi esterne per una data tabella:

 EXEC sp_fkeys 'TableName' 

Userei la funzione di Diagrammi del Database in SQL Server Management Studio, ma poiché hai escluso questo, questo ha funzionato per me in SQL Server 2008 (non ho 2005).

Per ottenere l’elenco dei nomi delle tabelle e delle colonne di riferimento …

 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 

Per ottenere i nomi dei vincoli di chiave esterna

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

Questo ti dà:

  • Lo stesso FK
  • Schema a cui appartiene l’FK
  • La ” tabella di riferimento ” o la tabella che ha l’FK
  • La ” colonna di riferimento ” o la colonna all’interno della tabella di riferimento che punta all’FK
  • La ” tabella di riferimento ” o la tabella che ha la colonna chiave a cui punta il tuo FK
  • La ” colonna di riferimento ” o la colonna che è la chiave a cui punta il tuo FK

Codice sotto:

 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 

Prova questo :

 sp_help 'TableName' 

Dovresti anche fare attenzione ai riferimenti ad altri oggetti.

Se la tabella ha avuto un elevato riferimento da altre tabelle, probabilmente è anche molto referenziata da altri oggetti come viste, stored procedure, funzioni e altro.

Consiglio davvero lo strumento GUI come la finestra di dialogo ‘visualizza dipendenze’ in SSMS o uno strumento gratuito come ApexSQL. Cerca questo perché la ricerca di dipendenze in altri oggetti può essere soggetta a errori se vuoi farlo solo con SQL.

Se SQL è l’unica opzione puoi provare a farlo in questo modo.

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

La domanda originale ha chiesto di ottenere un elenco di tutte le chiavi esterne in una tabella con riferimenti elevati in modo che la tabella possa essere rimossa.

Questa piccola query restituisce tutti i comandi “elimina chiave esterna” necessari per eliminare tutte le chiavi esterne in una determinata tabella:

 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' 

Esempio di output:

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

Omettere la clausola WHERE per ottenere i comandi di rilascio per tutte le chiavi esterne nel database corrente.

 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 

Ecco il codice SQL che vorrei usare.

 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 

SQL non è particolarmente chiaro, quindi diamo un’occhiata a un esempio.

Quindi, supponendo che volevo rilasciare la tabella Employees nell’amato database Northwind Microsoft, ma SQL Server mi ha detto che una o più Foreign Key mi impedivano di farlo.

Il comando SQL sopra restituirebbe questi risultati …

Keyes esteri

Mi mostra che ci sono 3 chiavi esterne che fanno riferimento alla tabella Employees . In altre parole, non sarei autorizzato a eliminare (eliminare) questa tabella fino a quando queste tre chiavi esterne non vengono prima eliminate.

Nei risultati, la prima riga mostra come verrà mostrato il seguente vincolo di chiave esterna nei risultati.

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

La penultima colonna mostra il comando SQL che avrei bisogno di usare per cancellare una di queste chiavi esterne, ad esempio:

 ALTER TABLE [Employees] DROP CONSTRAINT [FK_Employees_Employees] 

… e la colonna di destra mostra l’SQL per crearlo

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

Con tutti questi comandi, hai tutto il necessario per eliminare le chiavi esterne pertinenti per consentire di eliminare una tabella, quindi ricrearle in seguito.

Uff. Spero che questo ti aiuti.

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

Sto usando questo script per trovare tutti i dettagli relativi alla chiave esterna. Sto usando INFORMATION.SCHEMA. Di seguito è riportato uno script SQL:

 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 

Alcune buone risposte sopra. Ma preferisco avere la risposta con una query. Questo pezzo di codice è preso da sys.sp_helpconstraint (sys proc)

Questo è il modo in cui Microsoft cerca se ci sono chiavi esterne associate al 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 

La risposta sarà simile a questa: 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' 

Se vuoi ottenere la relazione di chiave esterna di tutte le tabelle, escludi la clausola where altrimenti scrivi il tuo tablename invece di tablename

Primo

 EXEC sp_fkeys 'Table', 'Schema' 

Quindi usa NimbleText per giocare con i tuoi risultati

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

Questo mostra solo la relazione se sono vincoli di chiave esterna. Apparentemente il mio database precede il vincolo FK. Alcune tabelle usano i trigger per imporre l’integrità referenziale e, a volte, non c’è altro che una colonna con un nome simile per indicare la relazione (e nessuna integrità referenziale).

Fortunatamente, abbiamo una scena di denominazione coerente, quindi sono in grado di trovare tabelle di riferimento e visualizzazioni come questa:

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

Ho usato questa selezione come base per la creazione di uno script che fa ciò che devo fare sulle tabelle correlate.

Lavorando su ciò che ho fatto @Gishu, sono stato in grado di produrre e utilizzare il seguente 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; 

Che Visualizza tutte le tabelle, le colonne e i nomi di chiavi esterne in 1 query.

Determina le chiavi primarie e le chiavi univoche per tutte le tabelle in un database …

Questo dovrebbe elencare tutti i vincoli e alla fine puoi mettere i tuoi filtri

 /* 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; 

Per riferimento, leggi thru – http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx

Lo sto usando dal 2008 in su. È simile ad alcune altre soluzioni elencate, ma i nomi dei campi sono appropriati per gestire le regole di confronto specifiche del caso (LatBin). Inoltre, è ansible alimentare un singolo nome tabella e recuperare solo le informazioni per quella tabella.

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

C’è come ottenere il conteggio di tutte le responsabilità per l’ID selezionato. Basta modificare il valore @dbTableName, il valore @dbRowId e il suo tipo (se int è necessario rimuovere ” nella riga n. 82 (..SET @SQL = ..)). Godere.

 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 

Questo ottiene qualsiasi chiave estranea che coinvolge la tabella scelta. * Presuppone un formato _FIRSTABLENAME_SECONDTABLENAME.

  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 ) 

Questa è una forma più generale:

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

La seguente soluzione funziona per 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