SQL Server Performance Forum – Threads Archive

stored procedure – backup the sql

SQL SERVER 2005
I want to backup the SQL for each stored procedure to an external source, I am probably going to use MS SourceSafe eventually and need to export all the sql from each stored procedure. The problem is that when i get a list of all the procedures and their SQL no carriage returns are stored and it is all on 1 line. I used the following SQL…
SELECT name,definition
FROM sys.sql_modules,sys.objects
WHERE sys.sql_modules.object_id = sys.objects.object_id AND TYPE = ‘P’
This means that when you want to re-create the procedure or look at the sql you have problems if you have used "–" to add a comment.
CREATE PROCEDURE RandomName AS
–Here is a comment
DECLARE @id INT
DECLARE @name VARCHAR(20)
becomes
CREATE PROCEDURE RandomName AS –Here is a comment DECLARE @id INT DECLARE @name VARCHAR(20) The Green bit being seen as the comment. Any ideas on getting the formatted version programmatically…???

Get the results to Text. ***********************
Dinakar Nethi
SQL Server MVP
***********************

Results to text only shows the top of each procedure, I want to bcp the results out singularly for each procedure, to a single text file… Any ideas how to do this…???

One of the things that Enterprise Manager is good at (for a change) … In the database, go to the Stored Procedures, select all. Now right-click, and select Generate SQL Script. Check the options – remember to include the permissions. Save to file.

Cheers Adriaan. I’ve done that before, what I’m trying to do is export all the stored procedures every night automatically into text files and have them in the text file as I see them in Management Studio when i alter/create a procedure… Thanks for looking though…

sorted, I bcp-ed it out anyway and it came out formatted correctly even though it doesn’t look like that in the grid… Thanks all for looking anyway. Tom

What is the need for exporting the stored procedures automatically every night? Madhivanan Failing to plan is Planning to fail

Not exporting nightly anymore, I’ve seen the fallacy in that… I was looking at Encrypting the Procedures and then everything would have to be done through MS SourceSafe but there doesn’t seem to be any free decryption code out there for SQL Server 2005 (just in case something happens)… plenty for 7 and 2000 but nothing later… There’s only 3 of us so will have to rely on good sense when editing procedures and hope everyone checks it out of SourceSafe and edits that version rather than using ALTER PROCEDURE in the Management Studio. Thanks
Tom

Here is the Procedure, just for the record… CREATE PROCEDURE exportAllTheProcedureSQL AS – It exports all the SQL individually to a file – named after the procedure. Each group of procedures – are in a subdirectory named after the database. – Change n

rocedureBackup to wherever you want to export to… DECLARE databaseCursor CURSOR FAST_FORWARD FOR SELECT name FROM master.dbo.sysdatabases (NOLOCK) ORDER BY 1 DECLARE @dbname VARCHAR(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> DECLARE @sql NVARCHAR(500) IF (EXISTS (SELECT TOP 1 * FROM tempdb.sys.all_objects (NOLOCK) WHERE name = ‘##procTemp’)) DROP TABLE ##procTemp; SELECT TOP 1 name dbname,name,definition INTO ##procTemp FROM sys.sql_modules (NOLOCK),sys.objects (NOLOCK) WHERE sys.sql_modules.object_id = sys.objects.object_id AND type = ‘P' TRUNCATE TABLE ##procTemp OPEN databaseCursor FETCH NEXT FROM databaseCursor INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = ‘INSERT INTO ##procTemp (dbname,name,definition) SELECT ”’[email protected]+”’,name,REPLACE(definition,”CREATE PROCEDURE”,”ALTER PROCEDURE”) FROM ‘[email protected]+’.sys.sql_modules a (NOLOCK),’[email protected]+’.sys.objects b (NOLOCK) WHERE a.object_id = b.object_id AND type = ”P” ORDER BY 1′ PRINT @dbname EXEC sp_executesql @sql FETCH NEXT FROM databaseCursor INTO @dbname END CLOSE databaseCursor DEALLOCATE databaseCursor DECLARE procCursor CURSOR FAST_FORWARD FOR SELECT dbname,name FROM ##procTemp (NOLOCK) ORDER BY 1,2 DECLARE @name VARCHAR(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> DECLARE @str VARCHAR(500) OPEN procCursor FETCH NEXT FROM procCursor INTO @dbname,@name WHILE @@FETCH_STATUS = 0 BEGIN PRINT ‘EXPORT ‘ + @dbname + ‘ ‘ + @name IF (EXISTS (SELECT TOP 1 * FROM tempdb.sys.all_objects (NOLOCK) WHERE name = ‘##procTemp2’)) DROP TABLE ##procTemp2; SELECT definition INTO ##procTemp2 FROM ##procTemp WHERE dbname = @dbname AND name = @name SET @str = ‘md n

rocedureBackup’[email protected] EXEC xp_cmdshell @str SET @str = ‘bcp "##procTemp2" out "n

rocedureBackup’ + @dbname + ” + @name + ‘.sql" -c -U "sa" -P "yourPassword"' EXEC xp_cmdshell @str FETCH NEXT FROM procCursor INTO @dbname,@name END CLOSE procCursor DEALLOCATE procCursor

There are procedures available on the net that use DMO to emulate the EM "Script" function, if you google I’m sure you can find something….

]]>