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?

23
demandé sur anar khalilov 2013-07-10 16:56:44

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.

81
répondu anar khalilov 2015-05-29 05:28:23

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!

3
répondu Mike Gledhill 2015-05-29 13:52:23

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'))
2
répondu Hank Freeman 2015-05-29 05:26:59

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 ***'
1
répondu Haseeb Ahmed 2017-04-13 07:33:58