How do you export the content of stored procedures?

Question

How can I export the content of a stored procedure using a SQL Statement?

Answer 1

If by 'Content' you are talking about the code, look into sp_helptext()

http://msdn.microsoft.com/en-us/library/ms176112.aspx

Here's a sample based on the old PUBS sample database

USE pubs;
GO
EXEC sp_helptext 'pubs.dbo.byroyalty';
GO

And here are the results

Text                                                                                      --------
CREATE PROCEDURE byroyalty @percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage

BUT, if by 'Content' you mean the results; you can either run the proc with the 'Results to File...' setting, or 'Results in Grid' and right click in the grid, and do a 'Save as...'

Answer 2

By the content you mean the code?

if so, you can call

EXEC sp_helptext proc_name

Note you can call it also with a view name

Answer 3

sp_helptext system stored procedure might help.

sp_helptext YourProcedureName

Also I use print to see my generated sql inside a stored procedure, if you're looking for something like it :

CREATE PROCEDURE YourProcedureName
AS
DECLARE @sql varchar(100)
SET @sql = 'SELECT * FROM MyTable'

print @sql
--EXEC @sql
GO

Answer 4

You can query the "sys.sql_modules" catalog view to find the SQL source code for your stored procs and stored funcs:

SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('your stored proc name here')

Marc