stored procedure – backup the sql | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

stored procedure – backup the sql

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.
–Here is a comment
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
<font color="blue">Here is the Procedure, just for the record…</font id="blue"><br /><br /><br />CREATE PROCEDURE exportAllTheProcedureSQL AS<br /><br />– It exports all the SQL individually to a file <br />– named after the procedure. Each group of procedures<br />– are in a subdirectory named after the database.<br /><br />– Change n:procedureBackup to wherever you want to export to…<br /><br />DECLARE databaseCursor CURSOR FAST_FORWARD FOR<br />SELECT name FROM master.dbo.sysdatabases (NOLOCK)<br />ORDER BY 1<br /><br />DECLARE @dbname VARCHAR(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />DECLARE @sql NVARCHAR(500)<br /><br />IF (EXISTS (SELECT TOP 1 * FROM tempdb.sys.all_objects (NOLOCK) WHERE name = ‘##procTemp’))<br />DROP TABLE ##procTemp;<br /><br />SELECT TOP 1 name dbname,name,definition<br />INTO ##procTemp<br />FROM sys.sql_modules (NOLOCK),sys.objects (NOLOCK)<br />WHERE sys.sql_modules.object_id = sys.objects.object_id AND type = ‘P'<br />TRUNCATE TABLE ##procTemp<br /><br />OPEN databaseCursor<br />FETCH NEXT FROM databaseCursor INTO @dbname<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br />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′<br />PRINT @dbname<br />EXEC sp_executesql @sql<br /><br />FETCH NEXT FROM databaseCursor INTO @dbname<br />END<br /><br />CLOSE databaseCursor<br />DEALLOCATE databaseCursor<br /><br />DECLARE procCursor CURSOR FAST_FORWARD FOR<br />SELECT dbname,name FROM ##procTemp (NOLOCK) ORDER BY 1,2<br /><br />DECLARE @name VARCHAR(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />DECLARE @str VARCHAR(500)<br /><br />OPEN procCursor<br />FETCH NEXT FROM procCursor INTO @dbname,@name<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br />PRINT ‘EXPORT ‘ + @dbname + ‘ ‘ + @name<br /><br />IF (EXISTS (SELECT TOP 1 * FROM tempdb.sys.all_objects (NOLOCK) WHERE name = ‘##procTemp2’))<br />DROP TABLE ##procTemp2;<br /><br />SELECT definition<br />INTO ##procTemp2<br />FROM ##procTemp<br />WHERE dbname = @dbname AND name = @name <br /><br />SET @str = ‘md n:procedureBackup’[email protected]<br />EXEC xp_cmdshell @str<br /><br />SET @str = ‘bcp "##procTemp2" out "n:procedureBackup’ + @dbname + ” + @name + ‘.sql" -c -U "sa" -P "yourPassword"'<br />EXEC xp_cmdshell @str<br /><br />FETCH NEXT FROM procCursor INTO @dbname,@name<br />END<br /><br />CLOSE procCursor <br />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….