T-SQL Удалить все таблицы

Я пытаюсь удалить все таблицы в базе данных без необходимости делать это в правильном порядке. Из того, что я прочитал, запуск команды NOCHECK предотвратит проверку внешних ключей. Однако даже после этого я все еще получаю сообщение об ошибке при попытке удалить первую таблицу.

Не удалось удалить объект "dbo.TABLENAME", поскольку на него ссылается ограничение FOREIGN KEY.

Я уже видел, как на этот вопрос успешно ответили, поэтому я не понимаю, чем отличается то, что я делаю. Это работает на SQL Server 2008 R2.

BEGIN TRANSACTION

--get current list of tables
SELECT QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) as 'Dropped Table'
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'


--disable constraint checking in all tables
DECLARE @sql NVARCHAR(max)
SET @sql = ''
SELECT @sql += ' ALTER TABLE ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + ' NOCHECK CONSTRAINT ALL; '
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'
select @sql
Exec sp_executesql @sql

--disable all constraints (this also didn't work)
--EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"


--drop all tables
SET @sql = ''
SELECT @sql += ' DROP TABLE ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + '; '
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'
select @sql
Exec sp_executesql @sql


--check current list, should be empty
SELECT QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) as 'Tables'
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'

ROLLBACK TRANSACTION

Обновление 1

Я удалил код отключения ограничения вместо кода удаления ограничения, но он дает ошибку.

--drop all constraints
DECLARE @sql NVARCHAR(max)
SET @sql = ''
SELECT @sql += ' ALTER TABLE ' +QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + ' DROP CONSTRAINT ' + ctu.CONSTRAINT_NAME + ';'
FROM sys.tables t
    JOIN sys.schemas s
        ON t.[schema_id] = s.[schema_id]
    INNER JOIN EOS_DEV.INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE as ctu
        ON ctu.TABLE_SCHEMA = s.name AND ctu.TABLE_NAME = t.name
WHERE  t.type = 'U'
Exec sp_executesql @sql

На ограничение «[CONSTRAINT_NAME]» ссылается таблица «[TABLE_NAME]», ограничение внешнего ключа «[FK_NAME]».

Как я могу изменить этот запрос, чтобы я ориентировался только на ограничения FK?


sql-server tsql foreign-keys sql-server-2008-r2 drop-table
person Swazimodo    schedule 14.06.2017    source источник
comment
Я бы попробовал сначала удалить все ограничения, а затем попробовать удалить таблицы.   -  person Praveen    schedule 14.06.2017
comment
@Praveen - это не весь вопрос, как удалить таблицы с помощью NOCHECK, не сбрасывая ограничения? Я думаю, что пользователь знает, что он может сначала отказаться от ограничений...   -  person Jacob H    schedule 14.06.2017
comment
Вы пробовали работать только с одной таблицей? Запустите exec sp_MSforeachtable @command1='alter table ? nocheck constraint all' и удалите одну таблицу. Он должен работать.   -  person Jacob H    schedule 14.06.2017
comment
Я бы попытался установить цикл, чтобы продолжать работать с exec sp_MSforeachtable «Drop Table?» которые проверяют наличие ошибки, возвращающей 0. Каждая итерация будет удалять ссылочные таблицы. На самом деле никогда не писал цикл, но обычно, если я хочу удалить все таблицы, я просто выполняю эту команду снова и снова, и в конце концов все они удаляются.   -  person Anthony Hancock    schedule 14.06.2017

Ответы (3)


arrow_upward
0
arrow_downward

Спасибо всем за вашу помощь. Я обновил свой запрос и теперь могу подтвердить, что он может удалять все таблицы без разбора. Я также добавил раздел, чтобы удалить все сохраненные процессы для небольшого добавления вкуса.

BEGIN TRANSACTION

--get current list of tables
SELECT QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) as 'Dropped Table'
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'

--drop all constraints
DECLARE @sql NVARCHAR(max)
SET @sql = ''
SELECT @sql += ' ALTER TABLE ' +QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + ' DROP CONSTRAINT ' + tc.CONSTRAINT_NAME + ';'
FROM sys.tables t
    JOIN sys.schemas s
        ON t.[schema_id] = s.[schema_id]
    INNER JOIN EOS_DEV.INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
        ON tc.TABLE_SCHEMA = s.name AND tc.TABLE_NAME = t.name
WHERE t.type = 'U'
    AND tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
Exec sp_executesql @sql

--drop all tables
SET @sql = ''
SELECT @sql += ' DROP TABLE ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + '; '
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'
Exec sp_executesql @sql

--drop all stored procs
SET @sql = ''
SELECT @sql += 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + ']'
FROM sys.procedures as p 
where p.is_ms_shipped = 0
    AND p.type = 'P'
Exec sp_executesql @sql

ROLLBACK TRANSACTION
person Swazimodo    schedule 16.06.2017

arrow_upward
-1
arrow_downward

Установка FK на NOCHECK позволит вам ВСТАВЛЯТЬ, ОБНОВЛЯТЬ или УДАЛИТЬ строки, которые нарушают ограничение. Это не позволит вам DROP или TRUNCATE целевой таблицы. НАПРИМЕР:

use tempdb

create table a(id int primary key)

create table b(id int primary key, aid int references a)

alter table b nocheck constraint all

insert into b(id,aid) values (1,1) --succeeds because of nocheck

drop table a --fails
--Msg 3726, Level 16, State 1, Line 11
--Could not drop object 'a' because it is referenced by a FOREIGN KEY constraint.
person David Browne - Microsoft    schedule 14.06.2017
comment
Спасибо за разъяснение, что происходит не так, но это не решение. - person Swazimodo; 15.06.2017

arrow_upward
-1
arrow_downward

Вы должны сделать это так сложно? Почему бы просто не восстановить пустую базу данных (или базу данных, содержащую только вашу схему и любые необходимые строки «по умолчанию»)?

person SMor    schedule 14.06.2017
comment
У меня нет доступа, и я не хочу писать формальный план реализации для DEV. - person Swazimodo; 15.06.2017