Как изменить схему всех таблиц, представлений и хранимых процедур в MSSQL

Недавно у нас возникли проблемы с нашим сервером базы данных, и после долгих усилий было решено изменить сервер базы данных. Таким образом, нам удалось восстановить базу данных на другом сервере, изменить строку подключения и т. Д. Все было запланировано, пока мы не попытаемся получить доступ к веб-сайту из веб-браузера.

Мы начали получать ошибки о том, что объекты базы данных не найдены. Позже мы выяснили, что это произошло в результате изменения имени схемы. Поскольку в базе данных Kentico имеется сотни объектов базы данных (таблицы, представления и хранимые процедуры), их невозможно вручную изменить один за другим. Есть ли практический способ сделать это?

Да, это возможно.

Чтобы изменить схему объекта базы данных, вам нужно запустить следующий SQL-скрипт:

ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.ObjectName 

Где ObjectName может быть именем таблицы, представлением или хранимой процедурой. Кажется, что проблема заключается в получении списка всех объектов базы данных с заданным именем shcema. К счастью, есть системная таблица с именем sys.Objects, которая хранит все объекты базы данных. Следующий запрос будет генерировать все необходимые SQL-скрипты для выполнения этой задачи:

 SELECT 'ALTER SCHEMA NewSchemaName TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];' FROM sys.Objects DbObjects INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id WHERE SysSchemas.Name = 'OldSchemaName' AND (DbObjects.Type IN ('U', 'P', 'V')) 

Где тип «U» обозначает пользовательские таблицы, «V» обозначает представления, а «P» обозначает хранимые процедуры.

Запуск вышеуказанного скрипта приведет к созданию команд SQL, необходимых для передачи объектов из одной схемы в другую. Что-то вроде этого:

 ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.CONTENT_KBArticle; ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.Proc_Analytics_Statistics_Delete; ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.Proc_CMS_QueryProvider_Select; ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.COM_ShoppingCartSKU; ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.CMS_WebPart; ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.Polls_PollAnswer; 

Теперь вы можете запустить все эти сгенерированные запросы для завершения операции передачи.

Вот SQL, который я запускал, чтобы переместить все таблицы в моей базе данных (разбросанные по нескольким схемам) в схему «dbo»:

 DECLARE @currentSchemaName nvarchar(200), @tableName nvarchar(200) DECLARE tableCursor CURSOR FAST_FORWARD FOR SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables ORDER BY 1, 2 DECLARE @SQL nvarchar(400) OPEN tableCursor FETCH NEXT FROM tableCursor INTO @currentSchemaName, @tableName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'ALTER SCHEMA dbo TRANSFER ' + @currentSchemaName + '.' + @tableName PRINT @SQL EXEC (@SQL) FETCH NEXT FROM tableCursor INTO @currentSchemaName, @tableName END CLOSE tableCursor DEALLOCATE tableCursor 

Уф!

Спасибо за подсказку. Вот мое обновление для того же самого, где я добавил crlf для вывода, а также поместил скобки вокруг SchemaName и ObjectName, потому что у некоторых объектов было «-» в названии, а скобки решили, что именование ошибка.

 SELECT 'ALTER SCHEMA NewSchemaName TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];' + CHAR(13)+ CHAR(10)+ 'GO '+ CHAR(13)+ CHAR(10) FROM sys.Objects DbObjects INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id WHERE SysSchemas.Name = 'OldSchemaName' AND (DbObjects.Type IN ('U', 'P', 'V')) 

Вы можете использовать следующий скрипт, просто скопируйте / вставьте для всех объектов

ПРИМЕЧАНИЕ . Вам нужно изменить имена схем в скрипте!

 DECLARE @OldSchema VARCHAR(200) DECLARE @NewSchema VARCHAR(200) DECLARE @SQL nvarchar(4000) SET @OldSchema = 'dbo' SET @NewSchema = 'Inf' DECLARE tableCursor CURSOR FAST_FORWARD FOR SELECT 'ALTER SCHEMA ['+ @NewSchema +'] TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];' AS Cmd FROM sys.Objects DbObjects INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id WHERE SysSchemas.Name = @OldSchema AND (DbObjects.Type IN ('U', 'P', 'V')) OPEN tableCursor FETCH NEXT FROM tableCursor INTO @SQL WHILE @@FETCH_STATUS = 0 BEGIN PRINT @SQL EXEC (@SQL) FETCH NEXT FROM tableCursor INTO @SQL END CLOSE tableCursor DEALLOCATE tableCursor PRINT '*** Finished ***'