registro - Cómo cambiar el esquema de todas las tablas, vistas y procedimientos almacenados en MSSQL

listar objetos sql server

Question

Recientemente tuvimos problemas en nuestro servidor de base de datos y, tras largos esfuerzos, se decidió cambiar el servidor de base de datos. Así que logramos restaurar la base de datos en otro servidor, cambiar la cadena de conexión, etc. Todo fue como se planeó hasta que intentamos acceder al sitio web desde un navegador web.

Comenzamos a recibir errores sobre los objetos de la base de datos que no se encuentran. Más tarde, descubrimos que ocurrió como resultado del nombre del esquema modificado. Dado que hay cientos de objetos de base de datos (tablas, vistas y procedimientos almacenados) en una base de datos de Kentico, no es posible cambiarlos todos manualmente, uno por uno. ¿Hay alguna forma práctica de hacer esto?

Answer 1

Aquí está el SQL que ejecuté, para mover todas las tablas de mi base de datos (repartidas en varios esquemas) al esquema "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 

¡Uf!

Answer 2

Gracias por la sugerencia. Aquí está mi actualización a la misma, donde agregué un crlf a la salida y puse corchetes alrededor de SchemaName y ObjectName, porque algunos de los objetos tenían un ''-'' en el nombre y los corchetes resolvieron esa denominación error.

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 3

Puede usar el siguiente script simplemente copiando / pegando para todos los objetos

NOTA : ¡Necesitas cambiar los nombres de esquema en el script!

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

Sí, es posible.

Para cambiar el esquema de un objeto de base de datos, debe ejecutar el siguiente script SQL:

ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.ObjectName

Donde ObjectName puede ser el nombre de una tabla, una vista o un procedimiento almacenado. El problema parece ser obtener la lista de todos los objetos de la base de datos con un nombre de shcema dado. Afortunadamente, hay una tabla del sistema llamada sys.Objects que almacena todos los objetos de la base de datos. La siguiente consulta generará todos los scripts SQL necesarios para completar esta tarea:

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

Donde el tipo ''U'' denota tablas de usuario, ''V'' denota vistas y ''P'' denota procedimientos almacenados.

La ejecución del script anterior generará los comandos SQL necesarios para transferir objetos de un esquema a otro. Algo como esto:

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;

Ahora puede ejecutar todas estas consultas generadas para completar la operación de transferencia.