Scripting individual stored procedures to file.

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 2005 Forums

Transact-SQL (2005)

Scripting individual stored procedures to file.

Author

Topic

pramod
Starting Member

3 Posts

Posted - 2009-07-02 : 05:14:38

Hi,
As all know that when we create a storedprocedure, it gets created in SQL server. I want to store these individual procedures in a individual text files.
For e.g. If have 3 procedures say, 1] GetEmployees. 2]GetEmployeebySupervisor. 3] CheckIfEmployeeNameExists. These procedures should be save in individual text files like GetEmployees.txt, GetEmployeebySupervisor.txt, CheckIfEmployeeNameExists.txt.
Like this I have around 200 stored procedure. I cannot open each of the SP's from Management studio and SAVE it in a file.
Is there any script which will save these stored procedure in individual text file.

Thanks in advance

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-07-02 : 05:23:23

moved from script library.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-02 : 05:27:21

haha...you need the tools from spirit1
http://www.ssmstoolspack.com/

AMITOFO+AMEN+YA ALLAH Hope the query works

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-02 : 05:28:46

try like this
right click on databasename --> tasks--> generate scripts-->next--> select databasename and click next --> select details--> check storeprocedures--> select required sp -->check scriptfile in that fileperobject --> mention path -->next --> finish

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-02 : 05:43:04

With small change, you can use
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/13/script-out-procedures-and-functions-part-2.aspx

Madhivanan

Failing to plan is Planning to fail

pramod
Starting Member

3 Posts

Posted - 2009-07-02 : 06:53:10

Thanks Madhivan. But how can I give dynamic value of text file while saving these SPs in individual file.I had made a moinor tweak for the script. Pls review and suggest.

declare @sps table(texts varchar(8000))
insert into @sps
select 'sp_helptext '''+name+'''' from sysobjects where xtype in ('p','fn')
order by xtype,name
create table scripts(sps varchar(8000))
declare @texts varchar(1000)
select @texts=min(texts)from @sps
while @texts>''
Begin
EXEC('insert into scripts(sps) EXEC '+ @texts)
insert into scripts(sps) select 'GO'
EXEC master..xp_cmdshell 'bcp "select * from ABC..scripts" queryout "c:\" ''+ texts +''-c -T'
select @texts=min(texts)from @sps where texts>@texts
End
drop table scripts

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-02 : 08:51:20

Try this

Try this

declare @sps table(texts varchar(8000))
insert into @sps
select 'sp_helptext '''+name+'''' from sysobjects where xtype in ('p','fn')
order by xtype,name
create table scripts(sps varchar(8000))
declare @texts varchar(1000)
declare @file_name varchar(1000)

select @texts=min(texts)from @sps
while @texts>''
Begin
set @file_name='proc_'+replace(replace(convert(varchar(30),getdate(),109),' ',''),':','')
waitfor delay '00:00:00:009'
EXEC('insert into scripts(sps) EXEC '+ @texts)
insert into scripts(sps) select 'GO'
EXEC master..xp_cmdshell 'bcp "select * from ABC..scripts" queryout "c:\'+ @file_name+'-c -T'
select @texts=min(texts)from @sps where texts>@texts
End
drop table scripts

Madhivanan

Failing to plan is Planning to fail

amitkrmadhukar
Starting Member

1 Post

Posted - 2010-08-22 : 07:14:04

type *.* > allSQL.sql is simple way to do,all files in one folder will get copied to single file called allsql.sql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources