SP_BAK

/*********************************************************************************   Name: SP_BAK_SVN Author: Sean D. Brian, ITX Enterprises  Purpose: Creates a separate file for each sp on your server's c: drive. Useful for initial source control setup.  ---------------------------------------------------------------------------- DISCLAIMER:  This code and information are provided "AS IS" without warranty of any kind, either expressed or implied, including but not limited to the implied  warranties or merchantability and/or fitness for a particular purpose. ---------------------------------------------------------------------------- LICENSE:  This script is free to download and use for personal, educational,  and internal corporate purposes, provided that this header is preserved.  Redistribution or sale of this index defrag script, in whole or in part, is  prohibited without the author's express written consent. *********************************************************************************/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_Def]') AND type in ('U'))BEGIN    DROP TABLE [dbo].[Proc_Def]END GOSET NOCOUNT ON;/*Notes:--1) Create a folder on the C: drive of the target server called SP_BAK--2) Change the Database Name below from YourDBName to the name of the target DB--3) Run the statement on the Target DB--4) If XP_cmdshell is blocked, use the statement below to enable it. --5) The output should now show a separate output for each file created.--Use when XP_cmdshell gets blocked-- To allow advanced options to be changed.EXEC sp_configure 'show advanced options', 1GO-- To update the currently configured value for advanced options.RECONFIGUREGO-- To enable the feature.EXEC sp_configure 'xp_cmdshell', 1GO-- To update the currently configured value for this feature.RECONFIGUREGO*/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 INTSET @PCOUNT=0CREATE TABLE Proc_Def (P_Count INT,Def TEXT)Open cfetch next from c into @IDwhile @@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 Def FROM YourDBName..Proc_Def" queryout "c:\SP_BAK\'+@ID+'.SQL" -c -T'        --PRINT @cmd        EXEC XP_cmdshell @cmd        fetch next from c into @ID    ENDclose c