Programmatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui

Question

Any pointers on how I can programmatically get exactly the identical stored procedure source from SQL Server 2005  as when I right-click on that stored procedure in SQL Server Management Studio and select modify   I m trying using SMO  but there are some textual differences   The procedure always has CREATE  not ALTER  and there are some differences in the header  such as missing GOs in the version I m getting programmatically   I can fix these up  but perhaps there is a better way   Again  I m in SQL Server 2005  using SMSE   Using SMO via Visual Studio 8 2008   Update   Gotten some answers that tell the basics of how to retrieve the stored procedure   What I m looking for is retrieving the text identical  or nearly identical  to what the GUI generates     Example   for sp mysp  right-click in Management Studio  select modify   This generates       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    I d like to programmatically get the same thing  notice the GOs in the header  and the fact that it s an ALTER PROCEDURE   Ideally  I d like to get this with minimal programmatic fixing up of the source retrieved   I d be happy to only get something that differed in the Script Date details

User · Answer

I saw a article via link  There are four methods  I just did a short summary here for helping other programmers   EXEC sp helptext  sp name    SELECT OBJECT ID  sp name    SELECT OBJECT DEFINITION  OBJECT ID  sp name     AS  Definition    SELECT   FROM sys sql modules WHERE object id   object id  sp name

User · Answer

You said programmatically  right  I hope C  is ok  I know you said that you tried SMO and it didn t quite do what you wanted  so this probably won t be perfect for your request  but it will programmatically read out legit SQL statements that you could run to recreate the stored procedure  If it doesn t have the GO statements that you want  you can probably assume that each of the strings in the StringCollection could have a GO after it   You may not get that comment with the date and time in it  but in my similar sounding project  big-ass deployment tool that has to back up everything individually   this has done rather nicely  If you have a prior base that you wanted to work from  and you still have the original database to run this on  I d consider tossing the initial effort and restandardizing on this output   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

User · Answer

EXEC sp helptext  your procedure name     This avoids the problem with INFORMATION SCHEMA approach wherein the stored procedure gets cut off if it is too long   Update  David writes that this isn t identical to his sproc   perhaps because it returns the lines as  records  to preserve formatting   If you want to see the results in a more  natural  format  you can use Ctrl-T first  output as text  and it should print it out exactly as you ve entered it   If you are doing this in code  it is trivial to do a foreach to put together your results in exactly the same way   Update 2  This will provide the source with a  CREATE PROCEDURE  rather than an  ALTER PROCEDURE  but I know of no way to make it use  ALTER  instead   Kind of a trivial thing  though  isn t it   Update 3  See the comments for some more insight on how to maintain your SQL DDL  database structure  in a source control system   That is really the key to this question

User · Answer

You will have to hand code it  SQL Profiler reveals the following   SMSE executes quite a long string of queries when it generates the statement    The following query  or something along its lines  is used to extract the text    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  and sp name N  test                                                                                                                   00003EE1  and SCHEMA NAME sp schema id  N dbo     It returns the pure CREATE which is then substituted with ALTER in code somewhere    The SET ANSI NULL stuff and the GO statements and dates are all prepended to this   Go with sp helptext  its simpler

User · Answer

EXEC sp helptext  your procedure name     This avoids the problem with INFORMATION SCHEMA approach wherein the stored procedure gets cut off if it is too long   Update  David writes that this isn t identical to his sproc   perhaps because it returns the lines as  records  to preserve formatting   If you want to see the results in a more  natural  format  you can use Ctrl-T first  output as text  and it should print it out exactly as you ve entered it   If you are doing this in code  it is trivial to do a foreach to put together your results in exactly the same way   Update 2  This will provide the source with a  CREATE PROCEDURE  rather than an  ALTER PROCEDURE  but I know of no way to make it use  ALTER  instead   Kind of a trivial thing  though  isn t it   Update 3  See the comments for some more insight on how to maintain your SQL DDL  database structure  in a source control system   That is really the key to this question

User · Answer

To alter a stored procedure  here s the C  code   SqlConnection con   new SqlConnection  your connection string    con Open    cmd CommandType   System Data CommandType Text  string sql   File ReadAllText YUOR SP SCRIPT FILENAME   cmd CommandText   sql     cmd Connection   con  cmd ExecuteNonQuery    con Close      Things to note    Make sure the USER in the connection string have the right to alter SP Remove all the GO SET ANSI NULLS XX SET QUOTED IDENTIFIER statements from the script file   If you don t  the SqlCommand will throw an error

User · Answer

I agree with Mark   I set the output to text mode and then sp HelpText  sproc    I have this binded to Crtl-F1 to make it easy

User · Answer

The Databse Publishing Wizard can dump the schema  and other objects  from the command line

User · Answer

I agree with Mark   I set the output to text mode and then sp HelpText  sproc    I have this binded to Crtl-F1 to make it easy

User · Answer

Use the following select statement to get the whole definition  select ROUTINE DEFINITION    from INFORMATION SCHEMA ROUTINES   where ROUTINE NAME    someprocname   I guess that SSMS and other tools read this out and make changes where necessary  such as changing CREATE to ALTER  As far as I know  SQL stores not other representations of the procedure

User · Answer

You said programmatically  right  I hope C  is ok  I know you said that you tried SMO and it didn t quite do what you wanted  so this probably won t be perfect for your request  but it will programmatically read out legit SQL statements that you could run to recreate the stored procedure  If it doesn t have the GO statements that you want  you can probably assume that each of the strings in the StringCollection could have a GO after it   You may not get that comment with the date and time in it  but in my similar sounding project  big-ass deployment tool that has to back up everything individually   this has done rather nicely  If you have a prior base that you wanted to work from  and you still have the original database to run this on  I d consider tossing the initial effort and restandardizing on this output   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

User · Answer

I agree with Mark   I set the output to text mode and then sp HelpText  sproc    I have this binded to Crtl-F1 to make it easy

User · Answer

I just want to note that instead of using find and replace to change create procedure to alter procedure  you are just as well to use a drop  you can put it right at the top and it does require text searching     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   If you are sure it s there  I guess you could just drop it too  but I wouldn t recommend that   Don t forget the go  since create procedure must be the first and only statement in a batch   Or the lazy approach   IF OBJECT ID N sp name   is not null     DROP sp name GO

User · Answer

I just want to note that instead of using find and replace to change create procedure to alter procedure  you are just as well to use a drop  you can put it right at the top and it does require text searching     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   If you are sure it s there  I guess you could just drop it too  but I wouldn t recommend that   Don t forget the go  since create procedure must be the first and only statement in a batch   Or the lazy approach   IF OBJECT ID N sp name   is not null     DROP sp name GO

User · Answer

Use the following select statement to get the whole definition  select ROUTINE DEFINITION    from INFORMATION SCHEMA ROUTINES   where ROUTINE NAME    someprocname   I guess that SSMS and other tools read this out and make changes where necessary  such as changing CREATE to ALTER  As far as I know  SQL stores not other representations of the procedure

User · Answer

You will have to hand code it  SQL Profiler reveals the following   SMSE executes quite a long string of queries when it generates the statement    The following query  or something along its lines  is used to extract the text    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  and sp name N  test                                                                                                                   00003EE1  and SCHEMA NAME sp schema id  N dbo     It returns the pure CREATE which is then substituted with ALTER in code somewhere    The SET ANSI NULL stuff and the GO statements and dates are all prepended to this   Go with sp helptext  its simpler

User · Answer

You will have to hand code it  SQL Profiler reveals the following   SMSE executes quite a long string of queries when it generates the statement    The following query  or something along its lines  is used to extract the text    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  and sp name N  test                                                                                                                   00003EE1  and SCHEMA NAME sp schema id  N dbo     It returns the pure CREATE which is then substituted with ALTER in code somewhere    The SET ANSI NULL stuff and the GO statements and dates are all prepended to this   Go with sp helptext  its simpler

User · Answer

I saw a article via link  There are four methods  I just did a short summary here for helping other programmers   EXEC sp helptext  sp name    SELECT OBJECT ID  sp name    SELECT OBJECT DEFINITION  OBJECT ID  sp name     AS  Definition    SELECT   FROM sys sql modules WHERE object id   object id  sp name

User · Answer

You will have to hand code it  SQL Profiler reveals the following   SMSE executes quite a long string of queries when it generates the statement    The following query  or something along its lines  is used to extract the text    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  and sp name N  test                                                                                                                   00003EE1  and SCHEMA NAME sp schema id  N dbo     It returns the pure CREATE which is then substituted with ALTER in code somewhere    The SET ANSI NULL stuff and the GO statements and dates are all prepended to this   Go with sp helptext  its simpler

User · Answer

EXEC sp helptext  your procedure name     This avoids the problem with INFORMATION SCHEMA approach wherein the stored procedure gets cut off if it is too long   Update  David writes that this isn t identical to his sproc   perhaps because it returns the lines as  records  to preserve formatting   If you want to see the results in a more  natural  format  you can use Ctrl-T first  output as text  and it should print it out exactly as you ve entered it   If you are doing this in code  it is trivial to do a foreach to put together your results in exactly the same way   Update 2  This will provide the source with a  CREATE PROCEDURE  rather than an  ALTER PROCEDURE  but I know of no way to make it use  ALTER  instead   Kind of a trivial thing  though  isn t it   Update 3  See the comments for some more insight on how to maintain your SQL DDL  database structure  in a source control system   That is really the key to this question

User · Answer

Use the following select statement to get the whole definition  select ROUTINE DEFINITION    from INFORMATION SCHEMA ROUTINES   where ROUTINE NAME    someprocname   I guess that SSMS and other tools read this out and make changes where necessary  such as changing CREATE to ALTER  As far as I know  SQL stores not other representations of the procedure

User · Answer

The Databse Publishing Wizard can dump the schema  and other objects  from the command line

User · Answer

The Databse Publishing Wizard can dump the schema  and other objects  from the command line

User · Answer

To alter a stored procedure  here s the C  code   SqlConnection con   new SqlConnection  your connection string    con Open    cmd CommandType   System Data CommandType Text  string sql   File ReadAllText YUOR SP SCRIPT FILENAME   cmd CommandText   sql     cmd Connection   con  cmd ExecuteNonQuery    con Close      Things to note    Make sure the USER in the connection string have the right to alter SP Remove all the GO SET ANSI NULLS XX SET QUOTED IDENTIFIER statements from the script file   If you don t  the SqlCommand will throw an error

User · Answer

I agree with Mark   I set the output to text mode and then sp HelpText  sproc    I have this binded to Crtl-F1 to make it easy

User · Answer

The Databse Publishing Wizard can dump the schema  and other objects  from the command line

User · Answer

EXEC sp helptext  your procedure name     This avoids the problem with INFORMATION SCHEMA approach wherein the stored procedure gets cut off if it is too long   Update  David writes that this isn t identical to his sproc   perhaps because it returns the lines as  records  to preserve formatting   If you want to see the results in a more  natural  format  you can use Ctrl-T first  output as text  and it should print it out exactly as you ve entered it   If you are doing this in code  it is trivial to do a foreach to put together your results in exactly the same way   Update 2  This will provide the source with a  CREATE PROCEDURE  rather than an  ALTER PROCEDURE  but I know of no way to make it use  ALTER  instead   Kind of a trivial thing  though  isn t it   Update 3  See the comments for some more insight on how to maintain your SQL DDL  database structure  in a source control system   That is really the key to this question

User · Answer

Use the following select statement to get the whole definition  select ROUTINE DEFINITION    from INFORMATION SCHEMA ROUTINES   where ROUTINE NAME    someprocname   I guess that SSMS and other tools read this out and make changes where necessary  such as changing CREATE to ALTER  As far as I know  SQL stores not other representations of the procedure

[sql-server] Programmatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?

Examples related to sql-server

Examples related to stored-procedures

Examples related to smo