Getting Stored Procedure Source in SQL Server

At some point you might find yourself outside of SQL Server Management Studio and need to get the contents of a stored procedure. Maybe you want to edit the proc or just list the source in sqlcmd. Either way, SQL Server includes two built-in procedures called "sp_helptext" and "sp_help" that are great resources in cases like this.

Below, I am getting the content of my stored procedure called "InsertNewUser":

                                        
  USE ProdDB
  exec sp_helpText 'InsertNewUser'

                                        

The above command will list all the source of the "InsertNewUser" procedure. On the other hand if you just want general information about a table or procedure "sp_help" may be the better option. When used with a table it will list information about columns, constraints, and even other tables that reference back to it via foreign keys. Using it with a stored procedure will list out parameter information including data types and value lengths:

                                        
  USE ProdDB
  exec sp_help 'UserTbl'
  exec sp_help 'InsertNewUser'

                                        

SQL Server includes a large number of built-in procedures like these that assist with database maintenance and management. More information on these other system procedures can be found here: SQL Server System Stored Procedures


© 2019 Embrs.net