Comment changer le schéma de toutes les tables, vues et procédures stockées dans MSSQL
récemment, nous avions des problèmes sur notre serveur de base de données et après de longs efforts, il a été décidé de changer le serveur de base de données. Nous avons donc réussi à restaurer la base de données sur un autre serveur, à modifier la chaîne de connexion, etc. Tout allait comme prévu jusqu'à ce que nous avons essayé d'accéder au site à partir d'un navigateur web.
nous avons commencé à recevoir des erreurs sur les objets de base de données n'étant pas trouvés. Plus tard, nous avons découvert que cela s'est produit à la suite du nom de schéma modifié. Depuis il y a des centaines d'objets de base de données (tables, vues et procédures stockées) dans une base de données Kentico, il n'est pas possible de les changer tous manuellement, un par un. Est-il un moyen pratique de faire cela?
4 réponses
Oui, c'est possible.
pour changer le schéma d'un objet de base de données, vous devez exécuter le script SQL suivant:
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.ObjectName
Où ObjectName peut être le nom d'une table, une vue ou d'une procédure stockée. Le problème semble être d'avoir la liste de tous les objets de base de données avec un shcema nom. Heureusement, il y a une table système appelée sys.Des objets qui stockent tous les objets de la base de données. La requête suivante générer tous les scripts SQL nécessaires pour accomplir cette tâche:
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'))
où le type " U "indique les tables d'utilisateurs," V "indique les vues et" P " indique les procédures stockées.
exécuter le script ci-dessus générera les commandes SQL nécessaires pour transférer des objets d'un schéma à un autre. Quelque chose comme ceci:
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;
Maintenant, vous pouvez exécuter toutes ces requêtes générées pour terminer l'opération de transfert.
voici le SQL que j'ai lancé, pour déplacer toutes les tables de ma base de données (réparties sur plusieurs schémas) dans le schéma" 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
Phew!
Merci pour le tuyau.. Voici ma mise à jour à same, où j'ai ajouté un crlf à la sortie ainsi que mis des crochets autour du nom de schéma et du nom D'objet, parce que certains des objets avaient un '-' dans le nom et les crochets ont résolu cette erreur de nom.
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'))
vous pouvez utiliser le script suivant juste copier / coller pour tous les objets
NOTE : vous devez changer les noms de schéma dans le 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 ***'