Если я хочу удалить все таблицы в моей базе данных, как это, будет ли он заботиться об ограничении внешнего ключа? Если нет, как я позабочусь об этом в первую очередь?
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]
Нет, это не приведет к потере вашей таблицы, если есть действительно внешние ключи, ссылающиеся на нее.
Чтобы получить все отношения внешних ключей, ссылающиеся на вашу таблицу, вы можете использовать этот SQL (если вы находитесь на SQL Server 2005 и выше):
SELECT *
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')
и если таковые имеются, с этим утверждением здесь вы можете создавать инструкции SQL, чтобы фактически отказаться от этих отношений 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')
В SQL Server Management Studio 2008 (R2) и новее вы можете щелкнуть правой кнопкой мыши по
DB → Задачи → Сгенерировать скрипты
Выберите таблицы, которые вы хотите DROP.
Выберите "Сохранить в новом окне запроса".
Нажмите кнопку "Дополнительно".
Установите Script DROP и CREATE в Script DROP.
Установите Script Внешние ключи в значение True.
Нажмите "ОК".
Нажмите "Далее" → "Далее" → "Готово".
Просмотрите Script и затем выполните.
Если вы сначала отбросите "дочернюю" таблицу, внешний ключ также будет отброшен. Если вы сначала попытаетесь сбросить "родительскую" таблицу, вы получите "Не удалось удалить объект" a ", потому что на него ссылается ограничение FOREIGN KEY." ошибка.
Вот еще один способ правильно отбросить все таблицы, используя процедуру sp_MSdropconstraints. Самый короткий код, который я мог бы подумать:
exec sp_MSforeachtable "declare @name nvarchar(max); set @name = parsename('?', 1); exec sp_MSdropconstraints @name";
exec sp_MSforeachtable "drop table ?";
Если это SQL Server, вы должны отказаться от ограничения, прежде чем вы сможете отбросить таблицу.
Здесь еще один способ удалить все ограничения, за которыми следуют сами таблицы, используя трюк конкатенации, включающий FOR XML PATH(''), который позволяет объединить несколько входных строк в одну выходную строку. Должен работать на любом SQL 2005 и более поздних версиях.
Я оставил команды EXECUTE закомментированными для безопасности.
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)
Вот полный script для реализации решения:
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 [email protected]
open cur1
fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
while @@fetch_status = 0
BEGIN
set @sql ='ALTER TABLE ' + @constraintSchemaName + '.' + @constraintTableName+' DROP CONSTRAINT '[email protected]+';'
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 [email protected]
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
Немного более общая версия того, что отправили @mark_s, это помогло мне
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')
просто подключите свое имя таблицы и выполните результат.
Если я хочу удалить все таблицы в моя база данных
Затем намного проще удалить всю базу данных:
DROP DATABASE WorkerPensions
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.
Если вы не возражаете потерять свои таблицы, вы можете использовать простой запрос для удаления сразу нескольких таблиц:
SET foreign_key_checks = 0;
DROP TABLE IF EXISTS table_a,table_b,table_c,table_etc;
SET foreign_key_checks = 1;
Таким образом, не имеет значения, в каком порядке вы используете таблицу в запросе.
Если кто-то собирается сказать что-то о том, что это нехорошее решение, если у вас есть база данных со многими таблицами: я согласен!
Если вы хотите DROP использовать таблицу, на которую ссылалась другая таблица с использованием внешнего ключа,
DROP TABLE *table_name* CASCADE CONSTRAINTS;
Я думаю, это должно сработать для вас.