To get all the stored procedure scripts

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

To get all the stored procedure scripts

Author

Topic

arthiasha
Starting Member

40 Posts

Posted - 2012-10-06 : 02:30:50

I had to get the scripts of all the stored procedure from a database in a single file say a .txt file.
I was able to list out all the stored procedure names in a file but i need some help of how to get all its script in a file.

select name from sys.all_objects where type = 'p'

this is to list out all the stored procedures which i got and i need to get the scripts of these procedures in a file,not using 'genenrate scripts...' but through a query.

Please get me the query

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-10-06 : 02:44:46

Try something like this:


SELECT OBJECT_DEFINITION(P.object_id)
  FROM sys.procedures AS P

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

arthiasha
Starting Member

40 Posts

Posted - 2012-10-06 : 02:58:07

Thanks a lot i am able to get the scripts

Can i have the procedure name concatenated with a string and get it in these script?
I so how do i give it along with this query.

select 'sp_helptext' + name FROM sys.all_objects WHERE type = 'P'

this query listed out all the sp names with the prefix sp_helptext.
How to have this concatenation along with
SELECT OBJECT_DEFINITION(P.object_id)
FROM sys.procedures AS P

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-10-06 : 03:11:08

Try something like this:


SELECT STUFF(S.script, CHARINDEX(P.name, S.script, 1), 0, N'sp_helptext')
  FROM sys.procedures AS P
       CROSS APPLY
       (SELECT OBJECT_DEFINITION(P.object_id) AS script) AS S

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

arthiasha
Starting Member

40 Posts

Posted - 2012-10-06 : 05:54:50

Thank you so much

arthiasha
Starting Member

40 Posts

Posted - 2012-10-06 : 06:18:43

Is this the same we give for user defined datatypes, if i need to get the scripts of userdefined tables,uddt, grant permissions?
For stored procedures we give it as 'P' and sys.procedures. what should be given for user defined datatypes, user defined tables?
Is there any websites i can refer for all?

arthiasha
Starting Member

40 Posts

Posted - 2012-10-06 : 08:05:56

Which system object should i use to get the scripts of user defined datatypes

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-10-06 : 08:31:44

checkout this http://scriptsqlconfig.codeplex.com/ by Graz

KH
[spoiler]Time is always against us[/spoiler]

arthiasha
Starting Member

40 Posts

Posted - 2012-10-07 : 12:39:45

As i got the query for extracting stored procedure scripts in single query i wanted to do get the same for user defined datatypes and user defined tables separately.
SELECT STUFF(S.script, CHARINDEX(P.name, S.script, 1), 0, N'sp_helptext')
FROM sys.procedures AS P
CROSS APPLY
(SELECT OBJECT_DEFINITION(P.object_id) AS script) AS S
What should i replace in this query to get all the scripts of user defined datatypes and user defined tables from a database

As i'm a beginner in mssql, please help me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources