INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQLDMO

How do I script all the objects from all databases on a server and record changes by nigelrivett
Posted: 1 Nov 03

Script database objects from tsql using sql-dmo
Author Nigel Rivett


This is for an uncontrolled environment where developers are allowed to change SPs without change control.
I would suggest to run the procedure every night.

Create a Database called Admin on the server to be scripted an place the SPs in it.
Schedule Admin..s_ScriptAllDatabases to run each night.

These procedures will script the fllowing objects in each database on a server via SQL DMO
Stored Procedures
User Defined Functions
Tables
Views
Indexes

You will need to create a schared directory for the output (the subdirectories will be created.
Also a work directory is required - suggest to put it local to the server.

After the script is run for the first time Add all the files to SourceSafe from the root directory
(Add, Recusive, Check out immediately).
After following script runs
    Check in, recursive, keep checked out
        (The default options will not create entries for unchanged files)
    Show differences, Show files that are only in the to location
        check in these files keeping them checked out

To script a server run s_ScriptAllDatabases as indicated in the comment.

Future enhancements
    Allow spaces in work directory path
    Allow remote scripting of a server
    Automatically update SourceSafe with the results


if exists (select * from sysobjects where id = object_id(N'[dbo].[s_ScriptAllDatabases]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ScriptAllDatabases]
GO

Create procedure s_ScriptAllDatabases
@SourceUID    varchar(128) ,    -- null for trusted connection
@SourcePWD    varchar(128) ,
@OutFilePath    varchar(256) ,    -- Root path - will add directory for object types
@OutFileName    varchar(128) ,     -- null for separate file per object script
@WorkPath    varchar(256)
as
/*
exec    s_ScriptAllDatabases
    @SourceUID    = null ,
    @SourcePWD    = null ,
    @OutFilePath    = '\\mymachine\nigel\mphamrsql03\' ,
    @OutFileName    = null ,
    @WorkPath    = 'e:\nigel\'                -- local to server - no spaces
*/

    if right(@OutFilePath,1) <> '\'
    begin
        select @OutFilePath = @OutFilePath + '\'
    end

    if right(@WorkPath,1) <> '\'
    begin
        select @WorkPath = @WorkPath + '\'
    end

    declare @tblDatabases table (name varchar(128))
    insert    @tblDatabases
        (name)
    select    name
    from    master..sysdatabases
    where    name <> 'tempdb'

declare    @FilePath    varchar(256) ,
    @cmd        varchar(1000)

declare    @name        varchar(128) ,
    @maxname    varchar(128)

    select    @name = '' ,
        @maxname = max(name)
    from    @tblDatabases

    while @name < @maxname
    begin
        select    @name = min(name) from @tblDatabases where name > @name

        select    @FilePath = @OutFilePath + '"' + @name + '"'

        -- output current database name
        select CurrentDatabase = @name

        -- create output directory - will fail if already exists but ...
        select    @cmd = 'mkdir ' + @FilePath
        exec master..xp_cmdshell @cmd

        exec    s_ScriptAllObjectsInDatabase
            @SourceDB     = @name ,
            @SourceUID     = @SourceUID ,
            @SourcePWD     = @SourcePWD ,
            @OutFilePath     = @FilePath ,
            @OutFileName     = @OutFileName ,    -- null for separate file per object script
            @WorkPath    = @WorkPath
    end
go

if exists (select * from sysobjects where id = object_id(N'[dbo].[s_ScriptAllObjectsInDatabase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ScriptAllObjectsInDatabase]
GO

Create procedure s_ScriptAllObjectsInDatabase
@SourceDB    varchar(128) ,
@SourceUID    varchar(128) ,    -- null for trusted connection
@SourcePWD    varchar(128) ,
@OutFilePath    varchar(256) ,    -- Root path - will add directory for object types
@OutFileName    varchar(128) ,     -- null for separate file per object script
@WorkPath    varchar(256)

as

    if right(@OutFilePath,1) <> '\'
    begin
        select @OutFilePath = @OutFilePath + '\'
    end

    if right(@WorkPath,1) <> '\'
    begin
        select @WorkPath = @WorkPath + '\'
    end

    set nocount on
    declare @tblObjectType table (ObjectType varchar(50))
    insert    @tblObjectType select 'PROCS'
    insert    @tblObjectType select 'FUNCTIONS'
    insert    @tblObjectType select 'TABLES'
    insert    @tblObjectType select 'VIEWS'
    insert    @tblObjectType select 'INDEXES'

declare    @FilePath    varchar(256) ,
    @cmd        varchar(1000)

declare    @ObjectType    varchar(50) ,
    @maxObjectType    varchar(50)

    select    @ObjectType = '' ,
        @maxObjectType = max(ObjectType)
    from    @tblObjectType

    while @ObjectType < @maxObjectType
    begin
        select    @ObjectType = min(ObjectType) from @tblObjectType where ObjectType > @ObjectType

        select    @FilePath = @OutFilePath + @ObjectType

        -- create output directory - will fail if already exists but ...
        select    @cmd = 'mkdir ' + @FilePath
        exec master..xp_cmdshell @cmd

        exec    s_ScriptObjects
            @SourceDB     = @SourceDB ,
            @SourceObject     = null ,
            @SourceUID     = @SourceUID ,
            @SourcePWD     = @SourcePWD ,
            @OutFilePath     = @FilePath ,
            @OutFileName     = null ,        -- null for separate file per object script
            @ObjectType     = @ObjectType ,
            @WorkPath    = @WorkPath
    end
go

if exists (select * from sysobjects where id = object_id(N'[dbo].[s_ScriptObjects]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ScriptObjects]
GO

Create procedure s_ScriptObjects
@SourceDB    varchar(128) ,
@SourceObject    varchar(128) ,    -- null for all objects
@SourceUID    varchar(128) ,    -- null for trusted connection
@SourcePWD    varchar(128) ,
@OutFilePath    varchar(256) ,
@OutFileName    varchar(128) ,  -- null for separate file per object script
@ObjectType    varchar(50) ,    -- PROCS, FUNCTIONS, TABLES, VIEWS, INDEXES
@WorkPath    varchar(256)
as
    set nocount on

declare    @SourceSVR    varchar(128) ,
    @ScriptType    int ,
    @FileName    varchar(256) ,
    @tmpFileName    varchar(256) ,
    @buffer        varchar(8000) ,
    @Collection    varchar(128) ,
    @id        int ,
    @name        varchar(128) ,
    @subname    varchar(128)

declare    @context    varchar(255) ,
    @sql        varchar(1000) ,
    @rc        int

    select @SourceSVR = @@servername

    if right(@OutFilePath,1) <> '\'
    begin
        select @OutFilePath = @OutFilePath + '\'
    end

    if right(@WorkPath,1) <> '\'
    begin
        select @WorkPath = @WorkPath + '\'
    end

    select    @SourceDB = replace(replace(@SourceDB,'[',''),'[','')

select    @ScriptType    = 4 | 1 | 64 ,
    @FileName    = @OutFilePath + @OutFileName ,
    @tmpFileName    = @WorkPath + 'ScriptTmp.txt'

declare    @objServer        int ,
    @objTransfer        int ,
    @strResult        varchar(255) ,
    @strCommand        varchar(255)

    -- get objects to script and object type
    create table #Objects (name varchar(128), subname varchar(128), id int identity(1,1))

    if @SourceObject is not null
    begin
        insert    #Objects
            (name)
        select @SourceObject
    end

    if @ObjectType = 'TABLES'
    begin
        if @SourceObject is null
        begin
            select @sql =         'insert    #Objects (name) '
            select @sql = @sql +     'select     TABLE_NAME '
            select @sql = @sql +     'from    [' + @SourceDB + '].INFORMATION_SCHEMA.TABLES '
            select @sql = @sql +     'where    TABLE_TYPE = ''BASE TABLE'''
            exec (@sql)
        end
        select @Collection = 'tables'
    end
    else if @ObjectType = 'PROCS'
    begin
        if @SourceObject is null
        begin
            select @sql =         'insert    #Objects (name) '
            select @sql = @sql +     'select     ROUTINE_NAME '
            select @sql = @sql +     'from    [' + @SourceDB + '].INFORMATION_SCHEMA.ROUTINES '
            select @sql = @sql +     'where    ROUTINE_TYPE = ''PROCEDURE'''
            exec (@sql)
        end
        select @Collection = 'storedprocedures'
    end
    else if @ObjectType = 'FUNCTIONS'
    begin
        if @SourceObject is null
        begin
            select @sql =         'insert    #Objects (name) '
            select @sql = @sql +     'select     ROUTINE_NAME '
            select @sql = @sql +     'from    [' + @SourceDB + '].INFORMATION_SCHEMA.ROUTINES '
            select @sql = @sql +     'where    ROUTINE_TYPE = ''FUNCTION'''
            exec (@sql)
        end
        select @Collection = 'userdefinedfunctions'
    end
    else if @ObjectType = 'VIEWS'
    begin
        if @SourceObject is null
        begin
            select @sql =         'insert    #Objects (name) '
            select @sql = @sql +     'select     TABLE_NAME '
            select @sql = @sql +     'from    [' + @SourceDB + '].INFORMATION_SCHEMA.VIEWS '
            select @sql = @sql +     'where    TABLE_NAME not like ''sys%'''
            exec (@sql)
        end
        select @Collection = 'views'
    end
    else if @ObjectType = 'INDEXES'
    begin
        if @SourceObject is null
        begin
            select @sql =         'insert    #Objects (name, subname) '
            select @sql = @sql +     'select     o.name, i.name '
            select @sql = @sql +     'from    [' + @SourceDB + ']..sysobjects o, [' + @SourceDB + ']..sysindexes i '
            select @sql = @sql +     'where    o.type = ''U'' '
            select @sql = @sql +     'and     i.id = o.id and i.indid <> 0 '
            select @sql = @sql +     'and     i.name not like ''_WA_%'''
            select @sql = @sql +     'and     o.name not like ''dtprop%'''
            select @sql = @sql +     'and     i.name not in (select name from [' + @SourceDB + ']..sysobjects)'
            exec (@sql)
        end
        select @Collection = 'tables'
    end
    else
    begin
        select 'invalid @ObjectType'
        return
    end

    -- create empty output file
    if @OutFileName is not null
    begin
        select    @sql = 'echo. > ' + @FileName
        exec master..xp_cmdshell @sql
    end

    -- prepare scripting object
    select @context = 'create dmo object'
    exec @rc = sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
    if @rc <> 0 or @@error <> 0 goto ErrorHnd

    if @SourceUID is null
    begin
        select @context = 'set integrated security ' + @SourceSVR
        exec @rc = sp_OASetProperty @objServer, LoginSecure, 1
        if @rc <> 0 or @@error <> 0 goto ErrorHnd
    end

    select @context = 'connect to server ' + @SourceSVR
    exec @rc = sp_OAMethod @objServer , 'Connect', NULL, @SourceSVR , @SourceUID , @SourcePWD
    if @rc <> 0 or @@error <> 0 goto ErrorHnd

    select @context = 'scripting'
    -- Script all the objects
    select @id = 0
    while exists (select * from #Objects where id > @id)
    begin
        select    @id = min(id) from #Objects where id > @id
        select @name = name, @subname = subname from #Objects where id = @id
        if @OutFileName is null
        begin
            select    @FileName = @OutFilePath + '"' + @name + coalesce('[' + @subname + ']','') + '.sql"'
            select    @sql = 'echo. > ' + @FileName
            exec master..xp_cmdshell @sql
        end
        select @sql = 'echo print ''Create = [' + @name + ']'+ coalesce('[' + @subname + ']','') + ''' >> ' + @FileName
        exec master..xp_cmdshell @sql
        if @ObjectType = 'INDEXES'
        begin
            Set @sql = 'databases("' + @SourceDB + '").' + @Collection + '("' + @name + '").indexes("' + @subname + '").script'
        end
        else
        begin
            Set @sql = 'databases("' + @SourceDB + '").' + @Collection + '("' + @name + '").script'
        end
        exec @rc = sp_OAMethod @objServer, @sql , @buffer OUTPUT, @ScriptType , @tmpFileName
        select @sql = 'type ' + @tmpFileName + ' >> ' + @FileName
        exec master..xp_cmdshell @sql
    end
    -- delete tmp file
    select @sql = 'del ' + @tmpFileName
    exec master..xp_cmdshell @sql

    -- clear up dmo
    exec @rc = sp_OAMethod @objServer, 'Disconnect'
    if @rc <> 0 or @@error <> 0 goto ErrorHnd

    exec @rc = sp_OADestroy @objServer
    if @rc <> 0 or @@error <> 0 goto ErrorHnd

    -- clear up temp table
    drop table #Objects

return
ErrorHnd:
select 'fail', @context

go



Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close