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":
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:
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