SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 To get all the stored procedure scripts
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arthiasha
Starting Member

India
40 Posts

Posted - 10/06/2012 :  02:30:50  Show Profile  Reply with Quote
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

Saudi Arabia
259 Posts

Posted - 10/06/2012 :  02:44:46  Show Profile  Reply with Quote
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
Go to Top of Page

arthiasha
Starting Member

India
40 Posts

Posted - 10/06/2012 :  02:58:07  Show Profile  Reply with Quote
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

Go to Top of Page

malpashaa
Constraint Violating Yak Guru

Saudi Arabia
259 Posts

Posted - 10/06/2012 :  03:11:08  Show Profile  Reply with Quote
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
Go to Top of Page

arthiasha
Starting Member

India
40 Posts

Posted - 10/06/2012 :  05:54:50  Show Profile  Reply with Quote
Thank you so much
Go to Top of Page

arthiasha
Starting Member

India
40 Posts

Posted - 10/06/2012 :  06:18:43  Show Profile  Reply with Quote
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?
Go to Top of Page

arthiasha
Starting Member

India
40 Posts

Posted - 10/06/2012 :  08:05:56  Show Profile  Reply with Quote
Which system object should i use to get the scripts of user defined datatypes
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 10/06/2012 :  08:31:44  Show Profile  Reply with Quote
checkout this http://scriptsqlconfig.codeplex.com/ by Graz


KH
Time is always against us

Go to Top of Page

arthiasha
Starting Member

India
40 Posts

Posted - 10/07/2012 :  12:39:45  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000