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

by 
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.

More Related Contents:

  1. T-SQL stored procedure that accepts multiple Id values
  2. Select columns from result set of stored procedure
  3. SQL Server: Query fast, but slow from procedure
  4. Escape a string in SQL Server so that it is safe to use in LIKE expression
  5. Retrieve column definition for stored procedure result set
  6. Dapper.NET and stored proc with multiple result sets
  7. How to list all dates between two dates [duplicate]
  8. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  9. EXEC sp_executesql with multiple parameters
  10. T-SQL Dynamic SQL and Temp Tables
  11. How do I find a stored procedure containing ?
  12. How do I search an SQL Server database for a string?
  13. Calling an API from SQL Server stored procedure
  14. Handling date in SQL Server
  15. SQL Server history table – populate through SP or Trigger?
  16. Retrieve data from stored procedure which has multiple result sets
  17. Calling stored procedure using VBA
  18. Stored Procedure and Permissions – Is EXECUTE enough?
  19. Avoid Naming User Stored Procedures SP% or SP_%
  20. Execute stored procedure with an Output parameter?
  21. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  22. Stored procedure – return identity as output parameter or scalar
  23. How to find a text inside SQL Server procedures / triggers?
  24. Python – pyodbc call stored procedure with parameter name
  25. SQL Server stored procedure return code oddity
  26. Populate Dataset With Table Names From Stored Procedure
  27. How to cleanse (prevent SQL injection) dynamic SQL in SQL Server?
  28. ODBC Call Failed with stored procedure – Pass through query
  29. Why can’t Entity Framework see my Stored Procedure’s column information?
  30. How to convert SQL Server’s timestamp column to datetime format

Leave a Comment