How do I script all database objects through dynamic T-SQL?

  • Hi Jason,

    We are doing a lot of this sort of thing. You can get create statements for views, stored procedures, user-defined functions, and possibly even table triggers--not sure about that last one--using SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.OBJ_NAME')).

    I've seen an article/script in the newsletter that gets this info by concatenating rows from syscomments, I think it is, but on the version I'm using (2005) the results are the same with OBJECT_DEFINITION, even on objects with large definitions.

    It's not difficult to generate a CREATE statement for tables using INFORMATION_SCHEMA.COLUMNS system view, but it gets more complicated if you need constraints and indexes.

    I haven't yet found a good way to copy an entire SQL agent job definition, and that's what I'm currently after.

  • We use the following code to create scripts for specific tables, views, stored procs, and functions. First we create an object list. Then each object is scripted out and the script sent to a text file. I'm not sure just how far this could be expanded. It does work in both SQL Server 2000 and 2005.

    I've "generisized" this a bit by removing the extra conditions we use when building the object list to limit this to only the specific objects in which we are interested.

    CREATE procedure [dbo].[ScriptObjects]

    (

    @SourceSVRvarchar(128), -- Source Server

    @SourceDBvarchar(128),

    @SourceObjectvarchar(128),

    @SourceUIDvarchar(128),

    @SourcePWDvarchar(128),

    @OutFilePathvarchar(128),

    @OutFileNamevarchar(128),

    @AddDateTimebit -- Add datetime to file name

    )

    AS

    set nocount on

    -- UNC paths are allowed in order to save files on other machines.

    -- Access must be granted to the account running MSSQL

    -- If drive letters are used (C:\), the file will be saved on the server

    -- actually running the stored procedure

    declare@ScriptTypeint ,

    @FileNamevarchar(128) ,

    @TmpFileNamevarchar(128) ,

    @buffervarchar(8000) ,

    @collectionvarchar(128)

    declare@contextvarchar(255) ,

    @sqlvarchar(8000) ,

    @rcint,

    @counterint,

    @objectmaxint

    declare@objServerint ,

    @objTransferint ,

    @strResultvarchar(255) ,

    @strCommandvarchar(255)

    IF @OutFileName IS NOT NULL AND @AddDateTime = 1

    set @OutFileName = @OutFileName + convert(varchar(8),getdate(),112) +

    LEFT(replace(convert(varchar(8),getdate(),108),':',''), 4) + '.sql'

    if (@OutFileName is null)

    set @OutFileName = 'cnv_script_' + @SourceDB + '_' + convert(varchar(8),getdate(),112) +

    LEFT(replace(convert(varchar(8),getdate(),108),':',''), 4) + '.sql'

    select@ScriptType= 1|4|64|256, -- 256 causes an append

    @FileName= @OutFilePath + @OutFileName ,

    @counter= 1

    -- get objects to script and object type

    create table ##Objects (ind int identity(1, 1), name varchar(128), collection varchar(128))

    SET @sql = 'Use ' + @SourceDB + '

    insert ##Objects (name, collection)

    SELECT TABLE_NAME, ''tables''

    FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_TYPE = ''BASE TABLE''

    ORDER BY TABLE_NAME

    insert ##Objects (name, collection)

    select ROUTINE_NAME, ''userdefinedfunctions''

    fromINFORMATION_SCHEMA.ROUTINES

    whereROUTINE_TYPE = ''FUNCTION''

    order by ROUTINE_NAME

    insert ##Objects (name, collection)

    select ROUTINE_NAME, ''storedprocedures''

    fromINFORMATION_SCHEMA.ROUTINES

    whereROUTINE_TYPE = ''PROCEDURE''

    order by ROUTINE_NAME

    insert ##Objects (name, collection)

    select TABLE_NAME, ''views''

    fromINFORMATION_SCHEMA.VIEWS

    order by TABLE_NAME'

    EXEC (@SQL)

    -- create empty output file

    select@sql = 'echo. > ' + @FileName

    exec master..xp_cmdshell @sql, no_output

    -- prepare scripting object

    exec @rc = sp_OACreate 'SQLDMO.SQLServer', @objServer OUT

    if @rc <> 0 or @@error <> 0 goto ErrorHnd

    --print 'object created'

    exec @rc = sp_OAMethod @objServer , 'Connect', NULL, @SourceSVR , @SourceUID , @SourcePWD

    if @rc <> 0 or @@error <> 0 goto ErrorHnd

    --print 'connection to ' + @SourceSVR + ' made'

    select @sql = 'echo USE ' + @SourceDB + ' >> "' + @FileName + '"'

    exec master..xp_cmdshell @sql, no_output

    -- Script all the objects

    SET @ScriptType= 1|4|64|256 -- 256 causes an append

    select @objectmax = isnull(max(ind),0) from ##Objects

    while (@counter <= @objectmax)

    begin

    select @SourceObject = name, @collection = collection from ##Objects where ind = @counter

    select @sql = 'echo print ''Create = ' + @SourceObject + ''' >> "' + @FileName + '"'

    exec master..xp_cmdshell @sql, no_output

    Set @sql = 'databases("' + @SourceDB + '").' + @collection + '("' + @SourceObject + '").script'

    exec @rc = sp_OAMethod @objServer, @sql , @buffer OUTPUT, @ScriptType , @FileName

    set @counter = @counter + 1

    end

    -- clear up dmo

    exec @rc = sp_OAMethod @objServer, 'Disconnect'

    if @rc <> 0 or @@error <> 0 goto ErrorHnd

    --print 'closing connection'

    exec @rc = sp_OADestroy @objServer

    if @rc <> 0 or @@error <> 0 goto ErrorHnd

    --print 'releasing object'

    -- clear up temp table

    drop table ##Objects

    return 0

    ErrorHnd:

    return -1

  • I wouldn't do this in TSQL. Most of the posts have DMO examples but you will need to use SMO since you need it for 2K5. Write it in one of the .Net languages and take advantage of the Script method for each object in the server. One of the posts mentioned Bill Wunders' site. I believe the SQL 2K5 DDL Archive portion of the SQL CLue tool is available as a beta on his site.

    I've written a console application in VB.Net for our shop that does exactly what you listed. I am not a VB programmer but I managed to muddle through it. if you are interested, PM me and I'll zip up the project and email it to you. It isn't very large but too much to post here.

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

  • Thank you ALL for your help.

    It has been very useful!

    I took a hybrid of some of your scripts, to get Stored PRocs / Views / Functions, a static snapshot of tables/indexes, and for the rest (Jobs, Maintenance Plans, etc.) We're going to write a .NET winforms application using SMO.

    It would have been nice to dynamically get the tables/indexes....however this will serve our purpose as the tables/indexes won't change very often.

    Thanks again!!

  • Just another thought, but it's worth mentioning. You could backup the

    source database and restore as the target database and delete all the

    data from the target database.

    The script below is found on this site and works well from what I tested.

    Script link http://www.sqlservercentral.com/scripts/T-SQL/61312/

    set nocount on

    declare @Order int

    Set @Order = 1

    IF Exists(SELECT * FROM tempdb.dbo.sysobjects WHERE [ID] = OBJECT_ID('tempdb.dbo.#a'))

    DROP TABLE #a

    IF Exists(SELECT * FROM tempdb.dbo.sysobjects WHERE [ID] = OBJECT_ID('tempdb.dbo.#b'))

    DROP TABLE #b

    create table #a ([id] int, [order] int)

    create table #b (fid int, rid int)

    -- First, delete from tables which are not referenced by any foreign key

    insert into #a

    select id, 0

    from sysobjects left join sysforeignkeys on id = rkeyid

    where objectproperty(id, 'isusertable') = 1

    and rkeyid is null

    -- Make a clone of key pairs from sysforeignkeys

    insert into #b

    select distinct fkeyid, rkeyid

    from sysforeignkeys

    -- While there are leaves tables do:

    while exists(select distinct f1.fid

    from #b f1 left join #b f2 on f1.fid = f2.rid

    where f2.rid is null)

    begin

    -- Insert leaves first

    insert into #a

    select distinct f1.fid, @Order

    from #b f1 left join #b f2 on f1.fid = f2.rid

    where f2.rid is null

    -- Delete just "ordered" tables

    delete f1

    from #b f1 left join #b f2 on f1.fid = f2.rid

    where f2.rid is null

    Set @Order = @Order + 1

    end

    -- Insert if something is rest in #b (e.g. self-join)

    insert into #a

    select distinct fid, @Order

    from #b

    -- Insert top level tables

    insert into #a

    select distinct f1.rkeyid, @Order + 1

    from sysforeignkeys f1 left join sysforeignkeys f2 on f1.rkeyid = f2.fkeyid

    where f2.rkeyid is null

    declare @id int

    DECLARE @DelCmd nvarchar(1000)

    -- Now when we have all tables in temporary table #a

    -- we can create dynamic script and execute it

    -- If you need list like this often you can create table-valued function

    -- starting code from this line, replacing temp table with function call

    declare c cursor

    for

    select [id], max([order])

    from #a

    group by [id]

    order by max([order]) asc

    open c

    --begin tran

    FETCH NEXT FROM c INTO @id, @Order

    WHILE (@@fetch_status <> -1)

    BEGIN

    IF (@@fetch_status <> -2)

    BEGIN

    SET @DelCmd = 'TRUNCATE TABLE ' + quotename(Object_name(@id))

    EXEC sp_executesql @DelCmd

    PRINT cast(quotename(Object_name(@id)) as char(50)) + cast(@@rowcount as char(7)) + ' row(s) deleted '

    END

    FETCH NEXT FROM c INTO @id, @Order

    END

    CLOSE c

    DEALLOCATE c

    --commit

    --rollback

    drop table #a

    drop table #b

    set nocount off

  • This might be over simplified but have you tried using the Transfer SQL Server Objects Task in SSIS? There is an option to transfer data which in your case you would set to false. You would then go through the other available options and choose which objects you want to transfer. Then you can have this package actually automate both scripting and creating the objects in your lab environment by importing the package to SQL and scheduling it in a SQL job.

    In this job you could drop and recreate the existing database and then run the package. One sticking point is transfering the database users. You just have to make sure that the logins are created on the destination server.

    Hope this helps.

    Regards.

  • I think there is a "Transfer Logins" task in SSIS as well.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Adam, in the bottom portion of your SQL you should be able to replace the entire cursor block with:

    EXEC sys.sp_MSforeachtable @command1='TRUNCATE TABLE ?'

  • Adam, in the bottom portion of your SQL you should be able to replace the entire cursor block with:

    EXEC sys.sp_MSforeachtable @command1='TRUNCATE TABLE ?'

    True, but not if foreign keys exist. The script I grabbed from this site checks for foreign keys and clears the tables, in order.

  • I agree that getting a vendor product to do the scripting is likely to be the most comprehensive answer.

    However if you want something free, take a look at SQLServerFineBuild. It includes SPs that can script most objects in a database. These can easily be wrapped in a SQL Agent job to run at a set time.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply