How to script out all your objects one per file

Fri Oct 24, 2008 by Peter Larsson in administration

/*******************************************************************************
Initialize communication variables
*******************************************************************************/

SET NOCOUNT ON

DECLARE @pathProc VARCHAR(255),
        @pathFunc VARCHAR(255),
        @pathTrig VARCHAR(255),
        @pathView VARCHAR(255),
        @cmd NVARCHAR(4000),
        @pathBase VARCHAR(256)

SELECT @pathBase = '\\Archive\Documents\Projects\Peso\Code\',
        @pathProc = @pathBase + 'Stored Procedures\',
        @pathFunc = @pathBase + 'Functions\',
        @pathTrig = @pathBase + 'Triggers\',
        @pathView = @pathBase + 'Views\'

SET   @cmd = 'md "' + @pathProc + '"'
EXEC   master..xp_cmdshell @cmd, no_output

SET    @cmd = 'md "' + @pathFunc + '"'
EXEC   master..xp_cmdshell @cmd, no_output

SET    @cmd = 'md "' + @pathTrig + '"'
EXEC   master..xp_cmdshell @cmd, no_output

SET    @cmd = 'md "' + @pathView + '"'
EXEC   master..xp_cmdshell @cmd, no_output

/*******************************************************************************
Stage all existing relevant code
*******************************************************************************/

CREATE TABLE TempDB..CodeOut
              (
                     spID INT,
                     [uID] INT,
                     colID INT,
                     codeText NTEXT,
                     isProc TINYINT,
                     isFunc TINYINT,
                     isTrig TINYINT,
                     isView TINYINT
              )

INSERT        TempDB..CodeOut
              (
                     spID,
                     [uID],
                     colID,
                     codeText,
                     isProc,
                     isFunc,
                     isTrig,
                     isView
              )
SELECT        so.id as spID,
              so.[uid] AS [uID],
              sc.colid AS colID,
              sc.text AS codeText,
              objectproperty(so.id, 'IsProcedure') AS isProc,
              objectproperty(so.id, 'IsScalarFunction') | objectproperty(so.id, 'IsTableFunction') AS isFunc,
              objectproperty(so.id, 'IsTrigger') AS isTrig,
              objectproperty(so.id, 'IsView')   AS isView
FROM          syscomments AS sc
INNER JOIN    sysobjects AS so ON so.id = sc.id
WHERE         objectproperty(so.id, 'IsEncrypted') = 0
              AND objectproperty(so.id, 'IsMSShipped') = 0
              AND objectproperty(so.id, 'IsExecuted') = 1
ORDER BY      so.id,
              sc.colid

CREATE UNIQUE CLUSTERED INDEX IX_Code ON TempDB..CodeOut(spID, colID)

SELECT COUNT(DISTINCT CASE WHEN isProc = 1 THEN spID ELSE NULL END) AS isProc,
       COUNT(DISTINCT CASE WHEN isFunc = 1 THEN spID ELSE NULL END) AS isFunc,
       COUNT(DISTINCT CASE WHEN isTrig = 1 THEN spID ELSE NULL END) AS isTrig,
       COUNT(DISTINCT CASE WHEN isView = 1 THEN spID ELSE NULL END) AS isView
FROM   TempDB..CodeOut

/*******************************************************************************
Initialize code variables
*******************************************************************************/

DECLARE @SQL NVARCHAR(4000),
        @spID INT,
        @fileName NVARCHAR(512),
        @objectName SYSNAME,
        @header NVARCHAR(1000),
        @static NVARCHAR(1000),
        @type NVARCHAR(20),
        @owner SYSNAME

SET    @static = 'USE ' + QUOTENAME(DB_NAME())
              + CHAR(13) + CHAR(10) + 'GO'
              + CHAR(13) + CHAR(10) + 'IF OBJECT_ID(''$owner.$object'') IS NOT NULL'
              + CHAR(13) + CHAR(10) + CHAR(9) + 'BEGIN'
              + CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) + 'PRINT ''DROP $type $owner.$object'''
              + CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) + 'DROP $type $owner.$object'
              + CHAR(13) + CHAR(10) + CHAR(9) + 'END'
              + CHAR(13) + CHAR(10) + 'PRINT ''CREATE $type $owner.$object'''
              + CHAR(13) + CHAR(10) + 'GO'
              + CHAR(13) + CHAR(10)

SELECT @spID = MIN(spID)
FROM    TempDB..CodeOut

/*******************************************************************************
Loop all code parts
*******************************************************************************/

WHILE @spID IS NOT NULL
       BEGIN
              SELECT TOP 1 @objectName = OBJECT_NAME(@spID),
                            @fileName = CASE 1
                                               WHEN w.isProc THEN @pathProc
                                               WHEN w.isFunc THEN @pathFunc
                                               WHEN w.isTrig THEN @pathTrig
                                               WHEN w.isView THEN @pathView
                                         END + COALESCE(su.name + '.', '') + PARSENAME(@objectName, 1) + '.sql',
                           @type =       CASE 1
                                         WHEN isProc THEN 'PROCEDURE'
                                         WHEN isFunc THEN 'FUNCTION'
                                         WHEN isTrig THEN 'TRIGGER'
                                         WHEN isView THEN 'VIEW'
                                  END,
                           @owner = QUOTENAME(su.name)
              FROM         TempDB..CodeOut AS w
              INNER JOIN   sysusers AS su ON su.uid = w.uid
              WHERE        w.spID = @spID

              SELECT @header = REPLACE(@static, '$type', @type),
                      @header = REPLACE(@header, '$owner', @owner),
                      @header = REPLACE(@header, '$object', QUOTENAME(@objectName))

              INSERT TempDB..CodeOut
                      (
                           spID,
                           colID,
                           codeText
                      )
              SELECT @spID,
                      0,
                      @header

              SELECT @SQL = 'SELECT codeText FROM TempDB..CodeOut WHERE spID = ' + CAST(@spID AS NVARCHAR(12)) + ' ORDER BY colID',
                      @cmd = 'BCP "' + @SQL + '" queryout "' + @filename + '" -S' + @@SERVERNAME + ' -T -CACP -w -t"" -r""'

              EXEC    master..xp_cmdshell @cmd, no_output

              SELECT @spID = MIN(spID)
              FROM    TempDB..CodeOut
              WHERE spID > @spID
       END

/*******************************************************************************
Clean up
*******************************************************************************/
DROP TABLE    TempDB..CodeOut

Legacy Comments


georgev 2008-10-24	re: How to script out all your objects one per file Hi Peter, Just noticed a small error in your code: the @header variable is set outside of the loop, and as such the same value is used for all files... I've added another variable called @header_static which takes the original value, then moified your replace commands as follows SET @header = REPLACE(@header_static, '$type', @type) SET @header = REPLACE(@header, '$owner', @owner) SET @header = REPLACE(@header, '$object', QUOTENAME(@objectName)) Other than that, this is class! Cheers, georgev

Peso 2008-10-24	re: How to script out all your objects one per file Thank you! I missed that completely. I have edited the code and discarded some old residues like minCol and maxCol too.

georgev 2008-10-24	re: How to script out all your objects one per file No trouble, What were the minCol and maxCol remnents of, out of interest?

Peso 2008-10-24	re: How to script out all your objects one per file That was code used before "ORDER BY colID" in the BCP part.

Josh Blair 2008-10-29	re: How to script out all your objects one per file Peso, this is great. Thanks. I wanted to mention that I had to tweak a few things to make it work on DB with collation set to Latin1_General_BIN. Also, what about scripting out tables, roles, users, etc? Thanks, Josh

Jacob 2008-10-29	re: How to script out all your objects one per file Thanks for the code but does not work here SET @cmd = 'md "' + @pathTrig + '"' EXEC master..xp_cmdshell@cmd, no_output Msg 170, Level 15, State 1, Line 21 Line 21: Incorrect syntax near ',' Could you please post the corrected version? Thanks Jacob

Peso 2008-10-29	re: How to script out all your objects one per file Jacob, put a space between xp_cmdshell and @cmd variable.

Peso 2008-10-29	re: How to script out all your objects one per file Josh, that is a great idea. I will see if I get the time to do that too.

latha 2008-10-29	re: How to script out all your objects one per file I want include Tables also. Please can you script for me

Peso 2008-11-14	re: How to script out all your objects one per file Meanwhile, look at this Microsoft KB article for information how to script out logins http://support.microsoft.com/kb/246133