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

EXEC sp_helptext 'nom de votre procédure';

Cela évite le problème avec l'approche INFORMATION_SCHEMA où la procédure stockée est coupée si elle est trop longue.

Mise à jour : David écrit que ce n'est pas identique à sa procédure... peut-être parce qu'il retourne les lignes comme des 'enregistrements' pour préserver le formatage ? Si vous souhaitez voir les résultats dans un format plus 'naturel', vous pouvez utiliser Ctrl-T d'abord (sortie en texte) et cela devrait l'imprimer exactement comme vous l'avez saisi. Si vous faites cela en code, il est trivial de faire une boucle foreach pour assembler vos résultats de la même manière.

Mise à jour 2 : Cela fournira la source avec un "CREATE PROCEDURE" plutôt qu'un "ALTER PROCEDURE" mais je ne connais pas de moyen de le faire utiliser "ALTER" à la place. C'est un peu trivial, n'est-ce pas ?

Mise à jour 3 : Consultez les commentaires pour avoir un aperçu supplémentaire sur la manière de maintenir votre DDL SQL (structure de base de données) dans un système de contrôle de source. C'est vraiment la clé de cette question.

Answer 2

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 la déclaration.

La requête suivante (ou quelque chose de similaire) est utilisée pour extraire le texte:

SELECT
NULL AS [Text],
ISNULL(smsp.definition, ssmsp.definition) AS [Définition]
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')and(sp.name=N'#test___________________________________________________________________________________________________________________00003EE1' and SCHEMA_NAME(sp.schema_id)=N'dbo')

Elle renvoie le pur CREATE qui est ensuite substitué par ALTER dans un code quelque part.

Les trucs SET ANSI NULL et les instructions GO et les dates sont tous ajoutés au début de cela.

Allez avec sp_helptext, c'est plus simple ...

Answer 3

Vous avez dit de manière programmable, n'est-ce pas? J'espère que C# convient. Je sais que vous avez dit que vous avez essayé SMO et que cela n'a pas tout à fait fait ce que vous vouliez, donc cela ne sera probablement pas parfait pour votre demande, mais cela lira de manière programmable des déclarations SQL légitimes que vous pourriez exécuter pour recréer la procédure stockée. S'il n'a pas les déclarations GO que vous voulez, vous pouvez probablement supposer que chacune des chaînes dans StringCollection pourrait avoir un GO après. Vous n'obtiendrez peut-être pas ce commentaire avec la date et l'heure, mais dans mon projet similaire (outil de déploiement énorme qui doit sauvegarder tout individuellement), cela a plutôt bien fonctionné. Si vous aviez une base précédente sur laquelle vous vouliez travailler, et que vous avez toujours la base de données d'origine sur laquelle exécuter ceci, je vous conseillerais de jeter l'effort initial et de vous réaligner sur cette sortie.

using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
…
string connectionString = … /* some connection string */;
ServerConnection sc = new ServerConnection(connectionString);
Server s = new Server(connection);
Database db = new Database(s, … /* database name */);
StoredProcedure sp = new StoredProcedure(db, … /* stored procedure name */);
StringCollection statements = sp.Script;

Answer 4

Utilisez la déclaration select suivante pour obtenir la définition complète :

select ROUTINE_DEFINITION 
  from INFORMATION_SCHEMA.ROUTINES 
 where ROUTINE_NAME = 'someprocname'

Je suppose que SSMS et d'autres outils lisent ceci et apportent des modifications si nécessaire, telles que changer CREATE en ALTER. Autant que je sache, SQL ne stocke pas d'autres représentations de la procédure.

Answer 5

Je suis d'accord avec Mark. J'ai réglé la sortie en mode texte puis sp_HelpText 'sproc'. Je l'ai lié à Crtl-F1 pour faciliter les choses.