Récupérer de manière programmable la source de la procédure stockée SQL Server qui est identique à la source retournée par l'interface graphique SQL Server Management Studio ?

Question

Des conseils sur la façon dont je peux obtenir programmation exactement le même code source de procédure stockée de SQL Server 2005, comme lorsque je clique droit sur cette procédure stockée dans SQL Server Management Studio et sélectionne modifier?

J'essaie d'utiliser SMO, mais il y a des différences textuelles. La procédure a toujours CREATE, pas ALTER, et il y a des différences dans l'en-tête, telles que des GO manquants dans la version que j'obtiens de manière programmatique. Je peux arranger ça, mais peut-être y a-t-il une meilleure façon?

Encore une fois, je suis dans SQL Server 2005, en utilisant SMSE. Utilisation de SMO via Visual Studio 8 2008.

Mise à jour: J'ai reçu quelques réponses qui expliquent les bases de la façon de récupérer la procédure stockée. Ce que je cherche, c'est la récupération du texte identique (ou presque identique) à ce que génère l'interface graphique.

Exemple : pour sp_mysp, clic droit dans Management Studio, sélectionnez modifier. Cela génère :

    USE \[MY\_DB\]  
    GO  
    /\*\*\*\*\*\* Object:  StoredProcedure \[dbo\].\[sp\_mysp\]    Script Date: 01/21/2009 17:43:18 \*\*\*\*\*\*/  
    SET ANSI\_NULLS ON  
    GO  
    SET QUOTED\_IDENTIFIER ON  
    GO  
    -- =============================================
    -- Author:      
    -- Create date: 
    -- Description: 
    -- =============================================
    ALTER PROCEDURE \[dbo\].\[sp\_mysp\]

J'aimerais obtenir de manière programmatique la même chose (remarquez les GOs dans l'en-tête, et le fait que c'est un ALTER PROCEDURE. Idéalement, j'aimerais obtenir cela avec un minimum de correction programmative du code source récupéré.

Je serais heureux d'obtenir quelque chose qui diffère dans les détails de la date du script . . .

Answer 1

Le Assistant de publication de base de données peut vider le schéma (et autres objets) à partir de la ligne de commande.

Answer 2

Je tiens juste à noter qu'au lieu d'utiliser la recherche et le remplacement pour changer create procedure en alter procedure, vous pouvez tout aussi bien utiliser un drop, vous pouvez le mettre juste en haut et cela ne nécessite pas de recherche de texte.

IF exists (SELECT * FROM sys.objects 
        WHERE object_id = OBJECT_ID(N'sp_name')
            and type in ('P','V') --procedure or view
        )
    DROP sp_name
GO

Si vous êtes sûr qu'il est là, je suppose que vous pourriez aussi le supprimer, mais je ne recommanderais pas cela. N'oubliez pas le "go", car create procedure doit être la première et la seule instruction dans un batch.

Ou l'approche paresseuse :

IF OBJECT_ID(N'sp_name') is not null
    DROP sp_name
GO

Answer 3

J'ai vu un article via lien. Il existe quatre méthodes, j'ai juste fait un bref résumé ici pour aider les autres programmeurs.

1. EXEC sp_helptext 'nom_sp';

2. SELECT OBJECT_ID('nom_sp')

3. SELECT OBJECT_DEFINITION( OBJECT_ID('nom_sp') ) AS [Définition];

4. SELECT * FROM sys.sql_modules WHERE object_id = object_id('nom_sp');

Answer 4

En fait, le serveur SQL est stocké dans le lecteur C où le logiciel SQL a été installé.

Vous devrez le coder à la main, le Profileur SQL révèle ce qui suit.

SMSE exécute une assez longue série de requêtes lorsqu'il génère l'instruction.

La requête suivante (ou quelque chose du même genre) est utilisée pour extraire le texte :

SELECT 
    NULL AS [Text], 
    ISNULL(smsp.definition, ssmsp.definition) AS [Definition] 
FROM sys.all_objects AS sp 
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id 
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id 
WHERE (sp.type = N'P' OR sp.type = N'RF' OR sp.type = 'PC')
et (sp.name = N'#test___________________________________________________________________00003EE1' 
et SCHEMA_NAME(sp.schema_id) = N'dbo')

Answer 5

Pour modifier une procédure stockée, voici le code C# :

SqlConnection con = new SqlConnection("votre chaîne de connexion");
con.Open();
cmd.CommandType = System.Data.CommandType.Text;
string sql = File.ReadAllText(NOM_FICHIER_SCRIPT_SP);
cmd.CommandText = sql;
cmd.Connection = con;
cmd.ExecuteNonQuery();
con.Close();

Choses à noter :

1. Assurez-vous que l'UTILISATEUR dans la chaîne de connexion a le droit de modifier la SP
2. Supprimez toutes les déclarations GO,SET ANSI_NULLS XX,SET QUOTED_IDENTIFIER du fichier de script. (Si vous ne le faites pas, le SqlCommand lancera une erreur).