0

I have a database which has a store of procedures in that particular database. Is there a way anyone can make a table of the names of all the stored procedures as a table, instead of manually typing each one. How would I write the query? Is there any other way of doing this?

Improve this question
2

3 Answers 3

Reset to default
2

Sean Lange is right you already have data in sys.procedures table. In case you still want to create a table: 

SELECT * INTO [Table_Name] FROM sys.procedures
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

There are many ways to get the list of stored procedures

Here after 3 ways: The last one is the old way, at the time of sql server 2000

select name,create_date,modify_date from sys.procedures select name,create_date,modify_date from sys.objects where type='p' select name,crdate,refdate from sysobjects where xtype='p'
Improve this answer

Comments

0

If you want more information besides just the names you can pull that too. For instance here is a query that pulls, the database, schema, procedure, definition, create date, and last modified date for all the stored procedures in a database. You could combine this with a call to the sys.sp_MSforeachdb stored proc to create a table for each database on a server.

SELECT DB_NAME() AS [Database], s.name AS [Schema], p.name AS [Procedure], ISNULL(d.definition,'***ENCRYPTED PROCEDURE***') AS [Definition], p.create_date, p.modify_date INTO [Table_Name] FROM sys.procedures AS p JOIN sys.sql_modules AS d on p.object_id = d.object_id JOIN sys.schemas AS s on p.schema_id = s.schema_id
Improve this answer

Comments