Introduction
Hi all,
Here how you can get all the stored procedure from a database. Hey not only the name but the stored procedures definitions also.
Get by using Cursor
declare @proc_name varchar(100)
declare @str_query nvarchar(MAX)
declare loop_proc cursor
for select [name] from sys.procedures where type='P'
and is_ms_shipped = 0
open loop_proc
FETCH NEXT FROM loop_proc INTO @proc_name
IF @@FETCH_STATUS <> 0
PRINT ' <>'
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @str_query = 'sp_helptext ' + @proc_name
PRINT @str_query
exec sp_executesql @str_query
FETCH NEXT FROM loop_proc INTO @proc_name
END
CLOSE loop_proc
DEALLOCATE loop_proc
Run these sql statements and view the result in Text mode. You will get all scripts for stored procedures.
Get from sysobjects
This is better option to get all stroed procedures from a database.
SELECT [text]
FROM
sysobjects o
JOIN syscomments c ON o.id = c.id
WHERE xtype = 'P'
Conclusion
So you learn how to get all stored procedures from a database, for getting more about sql tricks please visit http://matespoint.blogspot.com/search/label/SQL