February 21, 2008 at 7:14 am
Sushila ,Thanks a lot.
This option was very useful for me.
February 21, 2008 at 7:35 am
In Management Studio, right click the database you want to script.
Select Generate Scripts
Leave "script all objects..." unchecked
Next a couple of times
Stored Procedures
select the ones you want
When you get to Output Options, select Script to file, then select "File Per Object".
It will ask which directory to store them in and then will create one file per stored procedure you selected.
I definitely have these options in SQL 2005 Management Studio. If you don't, you might try updating your version of Management Studio. It might be something they added in later. I think it's been there all along, but I'm not sure.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 22, 2008 at 7:38 am
I wrote a nice little tool in vbscript that does exactly what you want (or you can tweak it accordingly). Try here, let me know if I can help further.
February 22, 2008 at 1:53 pm
The most easiest way to generate script of user objects in SQL Server I think is just to select the objects and right click -> copy and paste it into query analyzer. And you got your script.
If you want all objects scripts its just three click away.
Crtl + a, Crtl +c and Crtl + v. BINGO, you got it all.
only -ve point it does not script object permissions.
SQL DBA.
February 22, 2008 at 1:57 pm
$sanjayattray (2/22/2008)
The most easiest way to generate script of user objects in SQL Server I think is just to select the objects and right click -> copy and paste it into query analyzer. And you got your script.If you want all objects scripts its just three click away.
Crtl + a, Crtl +c and Crtl + v. BINGO, you got it all.
only -ve point it does not script object permissions.
Ooops........I didn't realize this post had got page 2, and was in assumption you are using SQL Server 2000, since the question is in 2000 forum.
SQL DBA.
November 19, 2008 at 5:03 am
***Abusive language removed by moderator***
March 2, 2010 at 2:54 am
HI guys, I am using SQL server2005. Thankx for sharing this info regarding saving all Stored procedure into file. I am getting all stored procedure in Single file. I want to save stored procedures into separate file. Please tell me how to do that? I am not getting option for saving stored procedure in separate file.
please help.
September 27, 2010 at 8:36 am
This works great for me too - thanks,
but - and you knew there was a but,
Is it possible to also export or otherwise copy, the definitions of all of the jobs?
We have a large system with lots of jobs and I would to "export" these so that if at some point,
if I need, I can go back and look at differences
thanks,
jon
March 30, 2011 at 1:04 am
Hi -
Not sure if you have got the answer to your query.
Enterprise Manager can generate a script for all the objects in the database (right click the database, "All tasks", "Generate SQL"). You could choose just the SProcs you want, and then have E.M. generate you a script (or it can put each object into a separate file, if you prefer)
Thanks,
Sanjeev.
May 26, 2011 at 10:39 am
This proc creates a mass creation script for all your objects
CREATE PROC ITX_SP_DS_SP_BAK
@P INT,
@FN INT,
@v-2 INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @t TABLE (Test INT)
declare c cursor local for
(
SELECT
name
FROM Sys.objects
WHERE
(
@FN=1 AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')
OR
@P=1 AND type IN (N'P', N'PC')
OR
@v-2=1 AND type IN (N'V')
)
AND name NOT LIKE '%sp_MSdel%' --exclude replication procs
AND name NOT LIKE '%sp_MSins%'
AND name NOT LIKE '%sp_MSupd%'
)
Declare @ID NVARCHAR(MAX)
CREATE TABLE Proc_Def (Def TEXT)
Open c
fetch next from c into @ID
while @@fetch_status = 0
BEGIN
INSERT Proc_Def
SELECT definition
FROM sys.sql_modules sm WITH ( NOLOCK )
LEFT JOIN sys.objects so ON so.object_id = sm.object_id
WHERE so.name=@ID
--EXEC sys.sp_helptext @objname = @ID
INSERT Proc_Def
SELECT'GO'
--Print OBJECT_DEFINITION(@ID))+' GO'
--PRINT @ID
fetch next from c into @ID
END
close c
EXEC XP_cmdshell 'BCP "SELECT * FROM Proc_Def" queryout "c:\SP_Script.SQL" -c -T'
DROP TABLE Proc_Def
SET NOCOUNT OFF;
END
May 26, 2011 at 10:41 am
This one is similar but not parameterized for different objects. It creates a separate .sql file for each object on your servers's C drive in the directory SP_BAK (which you must create before running this script, I don't want to be responsible for cluttering up your c drive)
SET NOCOUNT ON;
DECLARE @t TABLE (Test INT)
declare c cursor local for
(
SELECT
name
FROM Sys.objects
WHERE
type in (N'P', N'PC',N'FN', N'IF', N'TF', N'FS', N'FT')
AND name NOT LIKE '%sp_MSdel%' --exclude replication procs
AND name NOT LIKE '%sp_MSins%'
AND name NOT LIKE '%sp_MSupd%'
)
Declare @ID NVARCHAR(MAX)
DECLARE @PCOUNT INT
SET @PCOUNT=0
CREATE TABLE Proc_Def (P_Count INT,Def TEXT)
Open c
fetch next from c into @ID
while @@fetch_status = 0
BEGIN
SET @PCOUNT=@PCOUNT+1
INSERT Proc_Def
SELECT @PCOUNT p, definition+' GO'
FROM sys.sql_modules sm WITH ( NOLOCK )
LEFT JOIN sys.objects so ON so.object_id = sm.object_id
WHERE so.name=@ID
DELETE FROM PROC_DEF
WHERE P_Count!=@PCOUNT
DECLARE @cmd VARCHAR(255)
SET @cmd='BCP "SELECT * FROM Proc_Def" queryout "c:\SP_BAK\'+@ID+'.SQL" -c -T'
EXEC XP_cmdshell @cmd
fetch next from c into @ID
END
close c
DROP TABLE Proc_Def
SET NOCOUNT OFF;
November 23, 2011 at 2:01 am
I also used the BCP command with sp_executesql but I used sp_helptext in order to get sp code
http://www.kodyaz.com/t-sql/bcp-and-sp_helptext-to-script-store-procedures-into-seperate-files.aspx
But using the management views seems to be a better way of doing this.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply