Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

How to script out all your objects one per file

/*******************************************************************************
 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

Print | posted on Friday, October 24, 2008 2:04 PM | Filed Under [ Administration ]

Feedback

Gravatar

# 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
10/24/2008 2:40 PM | georgev
Gravatar

# 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.
10/24/2008 3:15 PM | Peso
Gravatar

# re: How to script out all your objects one per file

No trouble,

What were the minCol and maxCol remnents of, out of interest?
10/24/2008 3:29 PM | georgev
Gravatar

# re: How to script out all your objects one per file

That was code used before "ORDER BY colID" in the BCP part.
10/24/2008 3:34 PM | Peso
Gravatar

# 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
10/29/2008 6:40 PM | Josh Blair
Gravatar

# 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
10/29/2008 8:11 PM | Jacob
Gravatar

# re: How to script out all your objects one per file

Jacob, put a space between xp_cmdshell and @cmd variable.
10/29/2008 10:41 PM | Peso
Gravatar

# 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.
10/29/2008 10:42 PM | Peso
Gravatar

# re: How to script out all your objects one per file

I want include Tables also. Please can you script for me
10/29/2008 11:47 PM | latha
Gravatar

# 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
11/14/2008 8:33 AM | Peso
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET