Рефакторинг баз данных — удаление неиспользуемых таблиц в 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 комментария

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

    1. Стараюсь писать полезные статьи основанные на практических задачах. Рад, что вам понравилось.

Добавить комментарий для Cергей Отменить ответ

Ваш e-mail не будет опубликован. Обязательные поля помечены *