sql server - tablas - Restricción de la clave externa SQL DROP TABLE

relacionar tablas en sql server por codigo

Question

Si quiero eliminar todas las tablas de mi base de datos de esta manera, ¿se ocupará de la restricción de la clave externa? Si no, ¿cómo me ocupo de eso primero?

GO
IF OBJECT_ID(''dbo.[Course]'',''U'') IS NOT NULL
DROP TABLE dbo.[Course]
GO
IF OBJECT_ID(''dbo.[Student]'',''U'') IS NOT NULL
DROP TABLE dbo.[Student]

Answer 1

Si quiero eliminar todas las tablas en mi base de datos

Entonces es mucho más fácil eliminar toda la base de datos:

DROP DATABASE WorkerPensions

Answer 2

Aquí hay otra forma de eliminar todas las restricciones seguidas por las tablas, usando un truco de concatenación que involucra FOR XML PATH('''') que permite fusionar múltiples filas de entrada en una sola fila de salida. Debería funcionar en cualquier cosa SQL 2005 y posterior.

Dejé los comandos EJECUTAR comentados por seguridad.

DECLARE @SQL NVARCHAR(max)
;WITH fkeys AS (
SELECT quotename(s.name) + ''.'' + quotename(o.name) tablename, quotename(fk.name) constraintname
FROM sys.foreign_keys fk
JOIN sys.objects o ON fk.parent_object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
)
SELECT @SQL = STUFF((SELECT ''; ALTER TABLE '' + tablename + '' DROP CONSTRAINT '' + constraintname
FROM fkeys
FOR XML PATH('''')),1,2,'''')
-- EXECUTE(@sql)
SELECT @SQL = STUFF((SELECT ''; DROP TABLE '' + quotename(TABLE_SCHEMA) + ''.'' + quotename(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
FOR XML PATH('''')),1,2,'''')
-- EXECUTE(@sql)

Answer 3

Aquí hay otra forma de eliminar todas las tablas correctamente, utilizando el procedimiento sp_MSdropconstraints . El código más corto que pude pensar:

exec sp_MSforeachtable "declare @name nvarchar(max); set @name = parsename(''?'', 1); exec sp_MSdropconstraints @name";
exec sp_MSforeachtable "drop table ?";

Answer 4

Aquí hay un script completo para implementar una solución:

create Procedure [dev].DeleteTablesFromSchema
(
@schemaName varchar(500)
)
As
begin
declare @constraintSchemaName nvarchar(128), @constraintTableName nvarchar(128), @constraintName nvarchar(128)
declare @sql nvarchar(max)
-- delete FK first
declare cur1 cursor for
select distinct
CASE WHEN t2.[object_id] is NOT NULL THEN s2.name ELSE s.name END as SchemaName,
CASE WHEN t2.[object_id] is NOT NULL THEN t2.name ELSE t.name END as TableName,
CASE WHEN t2.[object_id] is NOT NULL THEN OBJECT_NAME(d2.constraint_object_id) ELSE OBJECT_NAME(d.constraint_object_id) END as ConstraintName
from sys.objects t
inner join sys.schemas s
on t.[schema_id] = s.[schema_id]
left join sys.foreign_key_columns d
on d.parent_object_id = t.[object_id]
left join sys.foreign_key_columns d2
on d2.referenced_object_id = t.[object_id]
inner join sys.objects t2
on d2.parent_object_id = t2.[object_id]
inner join sys.schemas s2
on t2.[schema_id] = s2.[schema_id]
WHERE t.[type]=''U''
AND t2.[type]=''U''
AND t.is_ms_shipped = 0
AND t2.is_ms_shipped = 0
AND s.Name=@schemaName
open cur1
fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
while @@fetch_status = 0
BEGIN
set @sql =''ALTER TABLE '' + @constraintSchemaName + ''.'' + @constraintTableName+'' DROP CONSTRAINT ''+@constraintName+'';''
exec(@sql)
fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
END
close cur1
deallocate cur1
DECLARE @tableName nvarchar(128)
declare cur2 cursor for
select s.Name, p.Name
from sys.objects p
INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id]
WHERE p.[type]=''U'' and is_ms_shipped = 0
AND s.Name=@schemaName
ORDER BY s.Name, p.Name
open cur2
fetch next from cur2 into @schemaName,@tableName
while @@fetch_status = 0
begin
set @sql =''DROP TABLE '' + @schemaName + ''.'' + @tableName
exec(@sql)
fetch next from cur2 into @schemaName,@tableName
end
close cur2
deallocate cur2
end
go

Answer 5

En SQL Server Management Studio 2008 (R2) y más reciente, puede hacer clic derecho en el

DB -> Tareas -> Generar scripts

• Seleccione las tablas que desea DROP.

• Seleccione "Guardar en nueva ventana de consulta".

• Haga clic en el botón Avanzado.

• Establezca SUN DROP y CREATE en Script DROP.

• Establezca Script Foreign Keys en True.

• Haga clic en Aceptar.

• Haga clic en Siguiente -> Siguiente -> Finalizar.

• Ver el script y luego ejecutar.

Answer 6

No, esto no soltará su tabla si de hecho hay claves externas que la referencian.

Para obtener todas las relaciones de clave foránea que hacen referencia a su tabla, puede usar este SQL (si está en SQL Server 2005 y versiones posteriores):

SELECT *
FROM sys.foreign_keys
WHERE referenced_object_id = object_id(''Student'')

y si hay alguno, con esta afirmación aquí, puede crear declaraciones SQL para realmente eliminar esas relaciones FK:

SELECT
''ALTER TABLE ['' + OBJECT_SCHEMA_NAME(parent_object_id) +
''].['' + OBJECT_NAME(parent_object_id) +
''] DROP CONSTRAINT ['' + name + '']''
FROM sys.foreign_keys
WHERE referenced_object_id = object_id(''Student'')

Answer 7

Si desea DROP una tabla a la que ha sido referenciada en otra tabla utilizando la clave externa use

DROP TABLE *table_name* CASCADE CONSTRAINTS;
Creo que debería funcionar para ti.

Answer 8

Si no le importa perder sus tablas, puede usar una consulta simple para eliminar varias tablas a la vez:

SET foreign_key_checks = 0;
DROP TABLE IF EXISTS table_a,table_b,table_c,table_etc;
SET foreign_key_checks = 1;

De esta forma, no importa en qué orden use la tabla en su consulta.

Si alguien va a decir algo sobre el hecho de que esta no es una buena solución si tiene una base de datos con muchas tablas: ¡estoy de acuerdo!

Answer 9

Si se trata de SQL Server debe eliminar la restricción antes de que pueda soltar la tabla.

Answer 10

Si suelta la tabla "hijo" primero, también se eliminará la clave externa. Si intenta soltar primero la tabla "principal", obtendrá un "No se pudo colocar el objeto ''a'' porque está referenciado por una restricción FOREIGN KEY". error.

Answer 11

Una versión un poco más genérica de lo que @mark_s publicó, esto me ayudó

SELECT
''ALTER TABLE '' + OBJECT_SCHEMA_NAME(k.parent_object_id) +
''.['' + OBJECT_NAME(k.parent_object_id) +
''] DROP CONSTRAINT '' + k.name
FROM sys.foreign_keys k
WHERE referenced_object_id = object_id(''your table'')

solo conecta el nombre de tu tabla y ejecuta el resultado.

Answer 12

Removing Referenced FOREIGN KEY Constraints
Assuming there is a parent and child table Relationship in SQL Server:
--First find the name of the Foreign Key Constraint:
SELECT *
FROM sys.foreign_keys
WHERE referenced_object_id = object_id(''States'')
--Then Find foreign keys referencing to dbo.Parent(States) table:
SELECT name AS ''Foreign Key Constraint Name'',
OBJECT_SCHEMA_NAME(parent_object_id) + ''.'' + OBJECT_NAME(parent_object_id) AS ''Child Table''
FROM sys.foreign_keys
WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = ''dbo'' AND
OBJECT_NAME(referenced_object_id) = ''dbo.State''
-- Drop the foreign key constraint by its name
ALTER TABLE dbo.cities DROP CONSTRAINT FK__cities__state__6442E2C9;
-- You can also use the following T-SQL script to automatically find
--and drop all foreign key constraints referencing to the specified parent
-- table:
BEGIN
DECLARE @stmt VARCHAR(300);
-- Cursor to generate ALTER TABLE DROP CONSTRAINT statements
DECLARE cur CURSOR FOR
SELECT ''ALTER TABLE '' + OBJECT_SCHEMA_NAME(parent_object_id) + ''.'' +
OBJECT_NAME(parent_object_id) +
'' DROP CONSTRAINT '' + name
FROM sys.foreign_keys
WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = ''dbo'' AND
OBJECT_NAME(referenced_object_id) = ''states'';
OPEN cur;
FETCH cur INTO @stmt;
-- Drop each found foreign key constraint
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@stmt);
FETCH cur INTO @stmt;
END
CLOSE cur;
DEALLOCATE cur;
END
GO
--Now you can drop the parent table:
DROP TABLE states;
--# Command(s) completed successfully.