How to get list of stored procedures in SQL Server?

How to get list of stored procedures in SQL Server?

This article presents two ways to return a list of stored procedures in a SQL Server database. You can use the ROUTINES information schema view to get a list of all user-defined stored procedures in a database.

Is there column for stored procedures in sys.objects?

The sys.objects view doesn’t include a column for the object’s definition. If you want to return each stored procedure’s definition, you can join it with the sys.sql_modules system view.

How to get the text of a stored procedure?

I would highly recommend just using the “Script To” function in SQL Server Management Studio: I have had great use of this in the past, when dealing with old objects like Stored Procedures. It sounds like you want to log the text of a procedure every time it changes. You can do this automatically with a DDL Trigger.

Can a procedure be called from the database?

Because OUTPUT parameters must be bound before an application can execute a procedure, procedures with cursor OUTPUT parameters cannot be called from the database APIs. These procedures can be called from Transact-SQL batches, procedures, or triggers only when the cursor OUTPUT variable is assigned to a Transact-SQL local cursor variable.

How to retrieve the table columns from a stored procedure?

Today, I came across an issue when I tried to get the metadata of a stored procedure in earlier version of SQL Server and wanted to use the list of columns for further manipulations. However, I could not find a proper solution like sys.dm_exec_describe_first_result_set in the earlier version of SQL Server.

How to extract the source text from SQL Server?

I recently had to extract the source code for more than two hundred stored procedures from a SQL Server database. Obviously I didn’t want to do that by hand so I wrote a simple script using sp_helptext: