una - restricciones sql
¿Cómo puedo averiguar qué restricción FOREIGN KEY hace referencia a una tabla en SQL Server? (14)
Intento dejar caer una tabla pero recibir el siguiente mensaje:
Msg 3726, nivel 16, estado 1, línea 3
No se pudo eliminar el objeto ''dbo.UserProfile'' porque está referenciado por una restricción FOREIGN KEY.
Msg 2714, nivel 16, estado 6, línea 2
Ya hay un objeto llamado ''UserProfile'' en la base de datos.
Miré a mi alrededor con SQL Server Management Studio pero no puedo encontrar la restricción. ¿Cómo puedo averiguar las restricciones de clave externa?
--Los siguientes pueden darle más de lo que está buscando:
create Procedure spShowRelationShips
(
@Table varchar(250) = null,
@RelatedTable varchar(250) = null
)
as
begin
if @Table is null and @RelatedTable is null
select object_name(k.constraint_object_id) ForeginKeyName,
object_name(k.Parent_Object_id) TableName,
object_name(k.referenced_object_id) RelatedTable,
c.Name RelatedColumnName,
object_name(rc.object_id) + ''.'' + rc.name RelatedKeyField
from sys.foreign_key_columns k
left join sys.columns c on object_name(c.object_id) = object_name(k.Parent_Object_id) and c.column_id = k.parent_column_id
left join sys.columns rc on object_name(rc.object_id) = object_name(k.referenced_object_id) and rc.column_id = k.referenced_column_id
order by 2,3
if @Table is not null and @RelatedTable is null
select object_name(k.constraint_object_id) ForeginKeyName,
object_name(k.Parent_Object_id) TableName,
object_name(k.referenced_object_id) RelatedTable,
c.Name RelatedColumnName,
object_name(rc.object_id) + ''.'' + rc.name RelatedKeyField
from sys.foreign_key_columns k
left join sys.columns c on object_name(c.object_id) = object_name(k.Parent_Object_id) and c.column_id = k.parent_column_id
left join sys.columns rc on object_name(rc.object_id) = object_name(k.referenced_object_id) and rc.column_id = k.referenced_column_id
where object_name(k.Parent_Object_id) =@Table
order by 2,3
if @Table is null and @RelatedTable is not null
select object_name(k.constraint_object_id) ForeginKeyName,
object_name(k.Parent_Object_id) TableName,
object_name(k.referenced_object_id) RelatedTable,
c.Name RelatedColumnName,
object_name(rc.object_id) + ''.'' + rc.name RelatedKeyField
from sys.foreign_key_columns k
left join sys.columns c on object_name(c.object_id) = object_name(k.Parent_Object_id) and c.column_id = k.parent_column_id
left join sys.columns rc on object_name(rc.object_id) = object_name(k.referenced_object_id) and rc.column_id = k.referenced_column_id
where object_name(k.referenced_object_id) =@RelatedTable
order by 2,3
end
Aquí está:
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) = ''YourTableName''
De esta manera, obtendrá la tabla de referencia y el nombre de la columna.
Editado para usar sys.tables en lugar de sys.objects genéricos según sugerencia de comentario. Gracias, marc_s
En SQL Server Management Studio puede hacer clic derecho en la tabla en el explorador de objetos y seleccionar "Ver dependencias". Esto te daría un buen punto de partida. Muestra tablas, vistas y procedimientos que hacen referencia a la tabla.
Encontré esta respuesta bastante simple e hice el truco para lo que necesitaba: https://.com/a/12956348/652519
Un resumen del enlace, use esta consulta:
EXEC sp_fkeys ''TableName''
Rápido y simple. Pude localizar rápidamente todas las tablas de claves externas, columnas respectivas y nombres de claves externas de 15 tablas.
Como se menciona a continuación en @mdisibio, aquí hay un enlace a la documentación que detalla los diferentes parámetros que se pueden usar: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys-transact-sql
Esta es la mejor manera de descubrir la relación de clave foránea en toda la base de datos.
exec sp_helpconstraint ''Table Name''
y una manera más
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME=''Table Name''
--and left(CONSTRAINT_NAME,2)=''FK''(If you want single key)
Estoy usando esta secuencia de comandos para encontrar todos los detalles relacionados con la clave externa. Estoy usando INFORMATION.SCHEMA. Debajo hay una secuencia de comandos 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
La forma más fácil de obtener Primary Key y Foreign Key para una tabla es:
/* Get primary key and foreign key for a table */
USE DatabaseName;
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME LIKE ''PK%'' AND
TABLE_NAME = ''TableName''
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME LIKE ''FK%'' AND
TABLE_NAME = ''TableName''
Otra forma es verificar los resultados de
sp_help ''TableName''
(o simplemente resalte el nombre de tabla citado y presione ALT + F1)
Con el tiempo, decidí refinar mi respuesta. A continuación se muestra una captura de pantalla de los resultados que sp_help ofrece. A ha utilizado la BD AdventureWorksDW2012 para este ejemplo. Hay mucha información buena allí, y lo que estamos buscando está al final - resaltado en verde:
Prueba esto
SELECT
object_name(parent_object_id) ParentTableName,
object_name(referenced_object_id) RefTableName,
name
FROM sys.foreign_keys
WHERE parent_object_id = object_id(''Tablename'')
Puede usar esta consulta para mostrar Foreign key constantes de las Foreign key foráneas:
SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = ''PRIMARY KEY''
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
ORDER BY
1,2,3,4
WHERE PK.TABLE_NAME=''YourTable''
También puede devolver toda la información sobre las Foreign Keys foráneas al adaptar la respuesta de @LittleSweetSeas:
SELECT
OBJECT_NAME(f.parent_object_id) ConsTable,
OBJECT_NAME (f.referenced_object_id) refTable,
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
order by
ConsTable
prueba la siguiente consulta.
select object_name(sfc.constraint_object_id) AS constraint_name,
OBJECT_Name(parent_object_id) AS table_name ,
ac1.name as table_column_name,
OBJECT_name(referenced_object_id) as reference_table_name,
ac2.name as reference_column_name
from sys.foreign_key_columns sfc
join sys.all_columns ac1 on (ac1.object_id=sfc.parent_object_id and ac1.column_id=sfc.parent_column_id)
join sys.all_columns ac2 on (ac2.object_id=sfc.referenced_object_id and ac2.column_id=sfc.referenced_column_id)
where sfc.parent_object_id=OBJECT_ID(<main table name>);
esto dará el constraint_name, column_names que se referirán y las tablas que dependerán de la restricción estarán allí.
si desea acceder a través de SSMS en la ventana del explorador de objetos, haga clic con el botón derecho en el objeto que desea eliminar, y ver las dependencias.
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;