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

Question

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

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

Answer 1

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

Чтобы изменить схему объекта базы данных, вам нужно запустить следующий скрипт 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;

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

Answer 2

Вот 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 

Уф!

Answer 3

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

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

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 ***'

Answer 4

Спасибо за подсказку.. Вот мое обновление для того же, где я добавил 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'))

Answer 5

Вот мой взгляд на объединение ответов Анара и Хэнка.
Я также изменил имена системных представлений и столбцов, поскольку использую SQL Server 2019, и кажется, что Microsoft изменила регистр первых букв для всех этих объектов.

      SELECT
    'ALTER SCHEMA NewSchemaName TRANSFER [' + ss.name + '].[' + so.name + '];' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM
    sys.objects so
INNER JOIN sys.schemas ss ON
    so.schema_id = ss.schema_id
WHERE
    ss.name = 'OldSchemaName'
    AND (so.type IN ('U', 'P', 'V'))