Как удалить таблицу в ms sql

Создание и удаление таблиц

Все данные в БД SQL Server хранятся в таблицах.
Таблицы состоят из колонок, объединяющих значения одного типа, и строк — записей в таблице. В одной БД может быть до 2 миллиардов таблиц, в таблице — 1024 колонки, в одной строке (записи) — 8060 байтов.

SQL Server поддерживает следующие типы данных:

Тип данных Обозначение Размер, байт

Бинарные данные binary
varbinary[(n)] 1-8000

Символы char[(n)]
varchar[(n)] 1-8000
( до 8000 символов)

Символы Unicode nchar[(n)]
nvarchar[(n)] 1-8000
(до 4000 символов)

Дата и время datetime
smalldatetime 8
4

Точные числа decimal[(p[,s])]
numeric[(p[,s])] 5-17

Приблизительные числа float[(n)]
real 4-8
4

Глобальный идентификатор uniqueidentifier 16

Целые числа int
smallint, tinyint 4
2, 1

Денежки . money, smallmoney 8, 4

Специальные bit, cursor,
sysname, timestamp 1, 0-8

Текст и изображение text, image 0-2 Гб

Текст Unicode ntext 0-2 Гб

Тип данных	Обозначение	Размер, байт
Бинарные данные	binary varbinary[(n)]	1-8000
Символы	char[(n)] varchar[(n)]	1-8000 ( до 8000 символов)
Символы Unicode	nchar[(n)] nvarchar[(n)]	1-8000 (до 4000 символов)
Дата и время	datetime smalldatetime	8 4
Точные числа	decimal[(p[,s])] numeric[(p[,s])]	5-17
Приблизительные числа	float[(n)] real	4-8 4
Глобальный идентификатор	uniqueidentifier	16
Целые числа	int smallint, tinyint	4 2, 1
Денежки .	money, smallmoney	8, 4
Специальные	bit, cursor, sysname, timestamp	1, 0-8
Текст и изображение	text, image	0-2 Гб
Текст Unicode	ntext	0-2 Гб

Таблицы можно создавать с помощью оператора CREATE TABLE языка Transact-SQL, а также с помощью Enterprise Manager. Рассмотрим сначала как это делается с помощью Transact-SQL.

Содание таблиц с помощью CREATE TABLE

Для создания таблиц применяется оператор CREATE TABLE.
Вот как выглядит упрощенный синтаксис этого оператора:

CREATE TABLE table_name (column_name data_type [NULL | NOT NULL] [. n])

CREATE TABLE member ( member_no int NOT NULL, lastname char(50) NOT NULL, firstname char(50) NOT NULL, photo image NULL )

member_no — имеет тип int, значения NULL не допускаются
lastname — имеет тип char(50) — 50 символов, значения NULL не допускаются
firstname — аналогично lastname
photo — имеет тип image (изображение), допускается значение NULL

Примечание

NULL — специальное обозначение того, что элемент данных не имеет значения. В описании типа колонки указывается, что элементы данных могут быть неинициализированы. При указании NOT NULL — «пустые» значения не допускаются. Если при вставке записи пропустить значение для такой колонки, вставка не произойдет, и SQL Server сгенерирует ошибку.

Попробуйте выполнить эту команду. Запустите Query Analyzer. Соединитесь с Вашим сервером. Из списка БД выберите sqlStep. Скопируйте в окно команд команду создания таблицы и выполните ее. (Если не забыли, надо нажать F5 или Ctrl-E).

Чтобы точно удостовериться, в том, что таблица была создана, наберите команду:

sp_help member

Выделите ее (как в обычном редакторе) и снова нажмите F5. В окно результатов будет выведена информация о таблице member.

На заметку!

sp_help — системная процедура, которая возвращает информацию об объектах БД (таблицах, хранимых процедурах и пр.).
Формат вызова таков:

sp_help

Удалить таблицу проще простого. Там же, в запросчике (так у нас называют Query Analyzer), наберите:

drop table member

Выделите эту строку и нажмите F5. Таблица будет удалена, о чем Вас и уведомят. В нашем случае эта процедура проста. На самом деле в большой БД просто так удалить таблицу не получится, поскольку она будет связана с другими таблицами, и для удаления потребуется эти связи оборвать. Как это сделать см. следующие шаги.

Как создать таблицу с помощью SQL Server Enterprise Manager

Раскройте последовательно: SQL Server Group, , Databases. Выберите БД (SqlStepByStep, я думаю :), нажмите правую кнопку мыши и выберите в контекстном меню пункт «New», а затем пункт «Table. «. Первым делом Вас спросят имя таблицы. Введите его и нажмите Enter. На экране появится окно, в котором можно вводить:
имена колонок, тип, длину, размерность, точность (эти три колонки блокируются в зависимости от типа), флаг разрешения NULL, значение по умолчанию. Последние три колонки, пока не представляют интереса.

Введите названия колонок, их тип и длину также как в примере выше. Нажмите на иконку с дискетой для сохранения таблицы и можете закрыть окно. Раскройте вашу БД, щелкните на категории «Tables» и в списке таблиц увидите только что введенную таблицу. Для ее удаления выделите ее в списке, нажмите правую кнопку мыши и в контекстном меню выберите «Delete». Таблица будет удалена.

Мы изучили как создаются и удалются таблицы. Следующий наш шаг — создание полноценной БД, на примере которой мы будем изучать:

что такое реляционная целостность БД и как она обеспечивается в SQL Server
как модифицировать данные в таблицах (операторы INSERT, UPDATE, DELETE, SELECT)
как использовать хранимые процедуры и триггеры

Справочник по MySQL : SQL : Удаление таблицы из базы данных MySQL (DROP TABLE)

Удаление таблицы производится командой DROP TABLE.

Синтаксис оператора DROP TABLE

DROP TABLE [IF EXISTS] tbl_name [, tbl_name. ] [RESTRICT | CASCADE]

Этот оператор удаляет таблицу или таблицы из текущей базы данных.

tbl_name — Имя удаляемой таблицы. IF EXISTS — Если указан этот параметр, то при попытке удаления несущестующей таблицы ошибки не возникнет. В противном случае возникнет ошибка выполнения команды. Данный параметр появился в MySQL начиная с версии 3.22. RESTRICT и CASCADE — Не несут никакой функциональности. Оставлены для упрощения переноса программы.

В следующем примере производится удаление таблицы users:

Как массово очистить все таблицы сразу в MsSql?

Всем привет!
Как массово очистить все таблицы сразу в MsSql?
Чтобы каждой таблице не писать truncate table.

Вопрос задан более трёх лет назад
1603 просмотра

Комментировать
Решения вопроса 0
Ответы на вопрос 3

AndyKorg

Кнопконажиматель и припоерасплавлятель
Что то типа такого:

declare @s nvarchar(max) = '' select @s = @s + 'truncate table '+TABLE_NAME+';' from INFORMATION_SCHEMA.TABLES exec (@s)

Ответ написан более трёх лет назад
Комментировать
Нравится 2 Комментировать
Готовые решения — не подаю, но.

Дурацкий, но «бронебойный» вариант:

DECLARE @execute_cmd varchar(max)= ''; -- выбрать один из трех вариантов по вкусу --SELECT @execute_cmd = @execute_cmd+'begin try truncate table ['+name+'] end try begin catch end catch ' FROM sys.tables WHERE type_desc = 'USER_TABLE'; --SELECT @execute_cmd = @execute_cmd+'begin try truncate table ['+TABLE_NAME+'] end try begin catch end catch ' FROM information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE'; --SELECT @execute_cmd = @execute_cmd+'begin try truncate table ['+Name+'] end try begin catch end catch ' FROM dbo.sysobjects WHERE xtype = 'U'; EXECUTE @execute_cmd;

и потом многократно «долбить» последнюю строку.

ибо foreign key и просто так удалить таблицу, на которую ссылаются данные из другой таблицы — не вариант

Рефакторинг баз данных — удаление неиспользуемых таблиц в SQL Server

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

Рефакторинг — процесс изменения внутренней структуры программы, не затрагивающий её внешнего поведения и имеющий целью облегчить понимание её работы.

Можно выделить несколько причин для внезапного рефакторинга, но как правильно, это одна причина: всех достал говнокод, который накопился со временем. С базами данных тоже самое — технический долг накапливается и рано или поздно его придется отдавать. Хорошо если начальство понимает необходимость рефакторинга и выделит на это драгоценное время. Если же не понимает то, все труднее. Но морально-технический аспект обработки заказчика оставим за рамками этой статьи.

У нас на работе есть проект (веб сайт и прочие сервисы), который использует базу данных с очень большим числом таблиц, а точнее более 800 штук. База развилась до такого размера не за один день, а за много лет работы. Со временем появились таблицы, которые перестали использоваться или вообще так и не были использованы с момента разработки когда-то очень нужной функциональности.
И вот в один прекрасный день заказчик, волевым решением ,ставит задачу удалить неиспользуемые таблицы не затрачивая лишнего времени. Задача понятна. Приступаем к реализации.

Но перед этим нужно ответить на несколько вопросов.

Вопрос 1. Как понять какие таблицы не используются?

Вручную анализировать код для 800 таблиц очень долго и сложно. Поэтому такой вариант сразу отпадает. Нужен автоматический вариант.

SQL Server хранит статистику использования(обращений) к таблицам с момента последней перезагрузки. Выполним запрос TSQL:

SELECT sqlserver_start_time AS LastSQLServiceRestart FROM sys.dm_os_sys_info

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

TSQL запрос возвращает имя таблицы, количество строк в таблице, дату создания и дату последнего изменения.

with UnUsedTables (TableName , TotalRowCount, CreatedDate , LastModifiedDate ) AS ( SELECT DBTable.name AS TableName ,PS.row_count AS TotalRowCount ,DBTable.create_date AS CreatedDate ,DBTable.modify_date AS LastModifiedDate FROM sys.all_objects DBTable JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id)=DBTable.name WHERE DBTable.type ='U' AND NOT EXISTS (SELECT OBJECT_ID FROM sys.dm_db_index_usage_stats WHERE OBJECT_ID = DBTable.object_id ) ) -- Select data from the CTE SELECT TableName , TotalRowCount, CreatedDate , LastModifiedDate FROM UnUsedTables ORDER BY TotalRowCount ASC

Полученный список таблиц сразу анализируем и предварительно отфильтровываем по дате создания, дате последнего использования и количестве строк.

Затем список копируем в Google Таблиц и даем доступ всем разработчикам, чтобы все могли его проверить и высказать свои замечания и предложения. После всех согласований формируем конечный список таблиц под удаление, а также утверждаем окончательный список у ответственных лиц. Полученный список сохраняем в документ Excel, так будет удобнее его обрабатывать.

Вопрос 2. Что будет если удалить все таки используемые таблицы?

Во первых, рабочие функции проекта, станут не рабочими. Только вот сразу об этом можно не узнать, если нет 100% покрытия тестами (у нас нет). Редкая функциональность может быть действительно редко используемой и для обнаружения бага может понадобиться месяц и более.

И во вторых , ответственный исполнитель получит по шапке. Хотя одобрение другими программистами частично снимет ответственность с бедолаги.

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

Перейдем к практической реализации задачи.

Массовое (множественное) переименование таблиц в SQL Server

Ручной вариант не рассматриваем. Для переименования одной таблицы нужно выполнить такой скрипт:

exec sp_rename @objname = TableName, @newname = NewTableName

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

=СЦЕПИТЬ("exec sp_rename @objname = ";A2;", @newname = z_";A2)

Для нового имени таблицы рекомендую использовать префикс «z_ » или подобный. Такое имя автоматом поместит таблицы в конец списка Server Management Studio.

Это один из вариантов генерации скрипта, вы можете использовать любой другой удобный вам способ.

Сгенерированный скрипт нужно выполнить. Во время выполнения появится такое сообщение: Caution: Changing any part of an object name could break scripts and stored procedures.

Это действительно так, вообще вся история с рефакторингом не гарантирует работоспособность системы. Тут надо руководствоваться правилом: «семь раз отмерь — один раз отрежь».

Основная проблема в том, что переименовываются только имена таблиц, а внешние ключи на эти таблицы остаются без изменений.

Массовое удаление таблиц в SQL Server

Через пол года можно уже без особого риска удалить таблицы, но предварительно лучше сделать бекап и положить его в безопасное место. Мало ли, что?!

Удаление одной таблицы:

Drop Table [TableName]

Сгенерировать TSQL скрипт для множественно удаления можно разными способами. Например, см выше использование Excel и формулы Сцепить. Тем более, что список таблиц под удаление известен и сохранен.

Можно воспользоваться, например, вот таким запросом:

SELECT 'DROP TABLE [' + TABLE_NAME + ']' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'z_%'

Важно заметить, что удаление не пройдет так гладко как переименование из-за зависимостей между таблицами. Важен правильный порядок удаления. Поэтому такой вариант удаления не самый лучший т.к. потребует много ручного труда. Возможно, он подойдет при не очень сложной структуре связей таблиц.

Более продвинутый вариант использовать средства Server Management Studio.

Select Database -> Right Click -> Tasks -> Generate Scripts — Откроется мастер генерирования скриптов. Выбираем нужные таблиц и нажимаем на кнопку Advanced

И выбираем опцию ‘Script DROP’. Выбираем куда поместить сгенерированный скрипт. В данном вопросе это не принципиально. Выбираю в новом окне.

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

На этом рефакторинг базы данных в части удаления устаревших таблиц можно считать законченным. Если данная статья была вам полезной проявите социальную активность) .

Рефакторинг баз данных — удаление неиспользуемых таблиц в SQL Server: 2 комментария

Cергей :

Работаю на фабрике программистом. У нас база гектар на 20 и таблиц там дофига -добро это все на sql server 2008. Эта статья прямо в тему! То что доктор базе прописал! Спасибо! Ждем еще чего-нибудь интересненького!