Create T-SQL Scripts for every table in the DB (in T-SQL)

create procedure dbo.dmoScriptTables
@pDatabaseName varchar(255),
@workingfolder varchar(255) = null,
        @pInstanceName varchar(30) = null
as
-- common
declare @dmoServer int,
@path varchar(255),
@cmd varchar(1200),
@returnstatus int,
@dmoMethod varchar(255), 
@dmoProperty varchar(255), 
@dmoCollection varchar(255), 
@scriptFile varchar(255),
@hr int,
@oleErrorSource varchar(255),
@oleErrorDescription varchar(1000),
@scriptType int,
@procedureName sysname,
        @Processflowerror varchar(255), 
        @pTempFolder varchar(255),
--tables
@isSystemTable tinyint,
@tableCount int,
@curTableNb int,
@tableName varchar(256),
@tableScriptType int,
@permissionsScriptType int,
        @firstTable bit,
        @permissionsScriptFile varchar(255),
-- keys
@keyCount int,
@curKeyNb int,
@keyName varchar(255),
@keyType int,
-- dridefaults
@columnCount int,
@curColumnNb int,
@objectName varchar(255),
@DRIDefaultName varchar(255),
-- indexes
@indexCount int,
@curIndexNb int,
@indexName varchar(256),
@indexType int,
-- checks
@checkCount int,
@curCheckNb int,
@checkName varchar(256),
-- triggers
@triggerCount int,
@curTriggerNb int,
@triggerName varchar(256)

-- Directory Structure Temp Directory 
Declare @prefix varchar(1000) 
Declare @prefix_fil  varchar (1000) 
Declare @prefix_tab varchar (1000) 
Declare @prefix_cns varchar (1000) 
Declare @prefix_viw varchar (1000) 
Declare @prefix_trg varchar (1000) 
Declare @prefix_rul varchar (1000) 
Declare @prefix_ind varchar (1000) 
Declare @prefix_prc varchar (1000) 
Declare @prefix_udf varchar (1000) 
Declare @prefix_def varchar (1000) 
Declare @prefix_ftc varchar (1000) 
Declare @prefix_rol varchar (1000) 
Declare @prefix_udt varchar (1000) 
Declare @prefix_usr varchar (1000) 
-- Memory Table For logging 
Declare @ActivityLog table 
( id int identity, 
  activity varchar(1000)) 

-- Ok here we begin with the stuff 

set nocount on 
set @Processflowerror = '' 
set @prefix = @workingFolder + '\DB-Framework\' 
set @prefix_fil  = @prefix + '01. Filegroups\' 
set @prefix_tab = @prefix + '02. Tables (only columns)\' 
set @prefix_cns = @prefix + '03. PK + FKs + Constraints\' 
set @prefix_viw = @prefix + '04. Views\' 
set @prefix_trg = @prefix + '06. Triggers\' 
set @prefix_rul = @prefix + '07. Rules\' 
set @prefix_ind = @prefix + '08. Indexes\' 
set @prefix_prc = @prefix + '09. Stored Procedures\' 
set @prefix_udf = @prefix + '10. User Defined Functions\' 
set @prefix_def = @prefix + '11. Defaults\' 
set @prefix_FTC = @prefix + '12. Full Text Catalogs\' 
set @prefix_rol = @prefix + '13. Roles\' 
set @prefix_UDT = @prefix + '14. User Defined Datatypes\' 
set @prefix_USR = @prefix + '15. Database users\' 

-- init
set @procedureName = db_name() + '.' 
+ user_name(objectproperty(@@procid,'OwnerId')) 
                + '.' + object_name(@@procid)

-- table script options
set @tableScriptType         = 4 -- primary object
                + 64 -- to
        + 512-- no DRI 
        + 4096 -- if not exists
        + 262144-- owner qualify

-- permissions script options
set @permissionsScriptType = 2 -- object permissions
        + 32 -- database (statement) permissions
--                              + 256           -- append (add this after first table inits the file)  
set @permissionsScriptFile = @path + 'allTablePermissions.sql'
set @firstTable = 0

-- constraint, index and trigger script options
set @scriptType         = 1-- drop 
        + 4-- primary object
        + 64-- to file only
        + 4096-- if not exists
        + 262144-- owner qualify

-- open an in-process COM/DMO connection to this server
exec @hr = master..sp_OACreate 'SQLDMO.SQLServer', @dmoServer OUT
if @hr <> 0 goto ErrorHandler

-- set the security context to integrated 
exec @hr = master..sp_OASetProperty @dmoServer,'loginSecure',1
if @hr <> 0 goto ErrorHandler

-- connect to the specified server 
exec @hr = master..sp_OAMethod @dmoServer,'Connect',NULL,@@servername
if @hr <> 0 goto ErrorHandler

-- script each table and table child object to a separate file
select @dmoProperty = 'Databases("' 
+ @pDatabaseName 
+ '").Tables.Count'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@tableCount OUT
if @hr <> 0 goto ErrorHandler
select @curTableNb = 1
while @curTableNb <= @tableCount   
begin
                print str(@Tablecount)
-- refresh the DMO buffers each iteration
select @dmoMethod = 'Databases("' 
+ @pDatabaseName 
+ '").Tables.Refresh(TRUE)'
exec @hr = master.dbo.sp_OAMethod @dmoServer,
@dmoMethod,
NULL
if @hr <> 0 goto ErrorHandler

select @dmoProperty = 'Databases("' 
+ @pDatabaseName 
+ '").Tables.Item(' 
+ cast(@curTableNb as varchar(10)) 
+ ').SystemObject'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@isSystemTable OUT
if @hr <> 0 goto ErrorHandler
-- only script user table
if @isSystemTable = 0 
begin
-- get the table name 
select @dmoProperty = 'Databases("' 
+ @pDatabaseName 
+ '").Tables.Item(' 
+ cast(@curTableNb as varchar(10)) 
+ ').Name'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@tableName OUT
if @hr <> 0 goto ErrorHandler
select @dmoMethod = 'Databases("' 
+ @pDatabaseName 
+ '").Tables("' 
+ @tableName 
+ '").Script'

-- put each table script in its own file 
select @scriptFile = @prefix_tab + @tableName + '.sql'
exec @hr = master.dbo.sp_OAMethod @dmoServer,
@dmoMethod,
NULL,
@tableScriptType,
@scriptFile
if @hr <> 0 goto ErrorHandler

-- put all table permissions in one file 
exec @hr = master.dbo.sp_OAMethod @dmoServer,
@dmoMethod,
NULL,
@permissionsScriptType,
@permissionsScriptFile
if @hr <> 0 goto ErrorHandler
                                -- append all remaining permissions to this file
                                if @firstTable = 0
                                        begin
                                                set @permissionsScriptType = @permissionsScriptType + 256
                                                set @firstTable = 1
                                        end
                                
--keys
set @keyType = 0
select @dmoProperty = 'Databases("' + 
@pDatabaseName 
+ '").Tables("' 
+ @tableName 
+ '").Keys.Count'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@keyCount OUT
if @hr <> 0 goto ErrorHandler
set @curKeyNb = 1
while @curKeyNb <= @keyCount 
begin
select @dmoProperty = 'Databases("' 
+ @pDatabaseName 
+ '").Tables("' 
+ @tableName 
+ '").Keys(' 
+ cast(@curKeyNb as varchar(10)) 
+ ').type'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@keyType OUT
if @hr <> 0 goto ErrorHandler
select @dmoProperty = 'Databases("' 
+ @pDatabaseName 
+ '").Tables("' 
+ @tableName 
+ '").Keys(' 
+ cast(@curKeyNb as varchar(10)) 
+ ').Name'
exec @hr = master.dbo.sp_OAGetProperty 
@dmoServer,
@dmoProperty,
@keyName OUT                
if @hr <> 0 goto ErrorHandler
if @keyName <> ''
begin
select @dmoMethod = 'Databases("' 
+ @pDatabaseName 
+ '").Tables("' 
                                        + @tableName 
+ '").Keys(' 
+ cast(@curKeyNb as varchar(10)) 
                                        + ').Script'
if @keyType = 1   
set @scriptFile = @prefix_cns + @keyName + '_PK.sql'
if @keyType = 2
set @scriptFile = @prefix_cns + @keyName + '_UNIQUE.sql'
if @keyType = 3
set @scriptFile = @prefix_cns + @keyName + '_FK.sql'
exec @hr = master.dbo.sp_OAMethod @dmoServer,
@dmoMethod,
NULL,
@scriptType,
@scriptFile
if @hr <> 0 goto ErrorHandler
end
set @curKeyNb = @curKeyNb + 1
end 

--DRI Defaults
set @curColumnNb = 1
select @dmoProperty = 'Databases("' 
+ @pDatabaseName 
+ '").Tables("' 
+ @tableName 
+ '").Columns.Count'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@columnCount OUT                
if @hr <> 0 goto ErrorHandler
while @curColumnNb <= @columnCount
begin
                select @dmoProperty = 'Databases("' 
+ @pDatabaseName 
+ '").Tables("' 
                        + @tableName 
+ '").Columns(' 
+ cast(@curColumnNb as varchar(10)) 
+ ').DRIDefault.Name'
                exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@DRIDefaultName OUT                
                if @hr <> 0 goto ErrorHandler
                if @DRIDefaultName <> ''
                        begin
                                select @scriptFile = @prefix_cns + @keyName + '_DRI.sql'
                                select @dmoMethod = 'Databases("' 
+ @pDatabaseName 
+ '").Tables("' 
                                        + @tableName 
+ '").Columns(' 
+ cast(@curColumnNb as varchar(10)) 
                                        + ').DRIDefault.Script'
                                exec @hr = master.dbo.sp_OAMethod @dmoServer,
@dmoMethod,
NULL,
@scriptType,
@scriptFile
                                if @hr <> 0 goto ErrorHandler
                        end
                set @curColumnNb = @curColumnNb + 1
        end

--Indexes
set @curIndexNb = 1
select @dmoProperty = 'Databases("' 
+ @pDatabaseName 
+ '").Tables("' 
+ @tableName 
+ '").Indexes.Count'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@indexCount OUT
if @hr <> 0 goto ErrorHandler
while @curIndexNb <= @indexCount
        begin
select @dmoProperty = 'Databases("' 
+ @pDatabaseName 
+ '").Tables("' 
+ @tableName 
+ '").Indexes(' 
+ cast(@curIndexNb as varchar(10)) 
+ ').Type'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@indexType OUT
if @hr <> 0 goto ErrorHandler
-- don't script Primary Keys or Unique Constraints here
if (not(@indexType & 2048 = 2048) 
and not(@indexType & 4096 = 4096))
begin
select @dmoProperty = 'Databases("' 
+ @pDatabaseName 
+ '").Tables("' 
+ @tableName 
+ '").Indexes(' 
+ cast(@curIndexNb as varchar(10)) 
+ ').Name'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@indexName OUT
if @hr <> 0 goto ErrorHandler
select @scriptFile = @prefix_ind + @indexName + '.IDX' 
select @dmoMethod = 'Databases("' 
+ @pDatabaseName 
+ '").Tables("' 
+ @tableName 
+ '").Indexes(' 
+ cast(@curIndexNb as varchar(10)) 
+ ').Script'
exec @hr = master.dbo.sp_OAMethod @dmoServer,
@dmoMethod,
NULL,
@scriptType,
@scriptFile
if @hr <> 0 goto ErrorHandler
end
set @curIndexNb = @curIndexNb + 1
end

--checks
set @curCheckNb = 1
select @dmoProperty = 'Databases("' 
+ @pDatabaseName 
+ '").Tables("' 
+ @tableName 
+ '").Checks.Count'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@checkCount OUT
if @hr <> 0 goto ErrorHandler
while @curCheckNb <= @checkCount
begin
select @dmoProperty = 'Databases("' 
+ @pDatabaseName 
+ '").Tables("' 
+ @tableName 
+ '").Checks(' 
+ cast(@curCheckNb as varchar(10)) 
+ ').Name'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@checkName OUT
if @hr <> 0 goto ErrorHandler
select @scriptFile = @prefix_cns+ @CheckName + '.chk' 
select @dmoMethod = 'Databases("' 
+ @pDatabaseName 
+ '").Tables("' 
+ @tableName 
+ '").Checks(' 
+ cast(@curCheckNb as varchar(10)) 
+ ').Script'
exec @hr = master.dbo.sp_OAMethod @dmoServer,
@dmoMethod,
NULL,
@scriptType,
@scriptFile
if @hr <> 0 goto ErrorHandler
set @curCheckNb = @curCheckNb + 1
end

--triggers
set @curTriggerNb = 1
select @dmoProperty = 'Databases("' 
+ @pDatabaseName 
+ '").Tables("' 
+ @tableName 
+ '").Triggers.Count'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@triggerCount OUT
if @hr <> 0 goto ErrorHandler
while @curTriggerNb <= @triggerCount
begin
select @dmoProperty = 'Databases("' 
+ @pDatabaseName 
+ '").Tables("' 
+ @tableName 
+ '").Triggers(' 
+ cast(@curTriggerNb as varchar(10)) 
+ ').Name'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@triggerName OUT
if @hr <> 0 goto ErrorHandler
select @scriptFile = @prefix_trg +  @triggerName + '.trg' 
select @dmoMethod = 'Databases("' 
+ @pDatabaseName 
+ '").Tables("' 
+ @tableName 
+ '").Triggers(' 
+ cast(@curTriggerNb as varchar(10)) 
+ ').Script'
exec @hr = master.dbo.sp_OAMethod @dmoServer,
@dmoMethod,
NULL,
@scriptType,
@scriptFile
if @hr <> 0 goto ErrorHandler

set @curTriggerNb = @curTriggerNb + 1
end
end
select @curTableNb = @curTableNb + 1
end

-- close and cleanup the COM/DMO database connection 
exec @hr = master.dbo.sp_OAMethod @dmoServer,'DisConnect' 
if @hr <> 0 
        goto ErrorHandler 
exec @hr = master.dbo.sp_OADestroy @dmoServer 
if @hr <> 0 
        goto ErrorHandler 
-- audit completion 
select * from @Activitylog 
return 

ErrorHandler: 
insert @ActivityLog (activity) values ('Command Was :'+@Processflowerror) 
if (@hr is not null) 
        begin   
                exec master.dbo.sp_OAGetErrorInfo       @dmoServer, 
                                                        @OleErrorSource OUT, 
                                                        @OleErrorDescription OUT 
                
                insert @ActivityLog (activity) 
                select @procedureName + ' ' + @pDatabaseName 
                        + ' ended with error: ' + cast(@hr as varchar(20)) + ' 
                        OLE ERROR: ' 
                --+ isnull(Admin.dbo.binToHex (@hr),'not defined') no function in 7 so skip conversion of error number 
                + cast(@hr as varchar(20)) + ' 
                        Source: ' + isnull(@OleErrorSource,'unknown') + ' 
                        Description: ' + isnull(@OleErrorDescription,'unknown') 

                -- still need to cleanup 
                exec master.dbo.sp_OAMethod @dmoServer,'DisConnect' 
                exec master.dbo.sp_OADestroy @dmoServer 

                raiserror (59001,16,1,@procedureName) 
        end 
else 
        if @cmd is not null 
                begin 
                        insert @ActivityLog (activity) 
                        select @procedureName + ' ' + @pDatabaseName 
                                + ' ' + isNull(@pTempFolder,'') 
                                + ' failed with returnstatus ' 
                                + cast(@returnstatus as varchar(10)) + ' at: ' + @cmd 
                        raiserror(59001,16,1,@procedureName) 
                end 
        else 
                raiserror (59000,16,1,@procedureName) 
select * from @activitylog 
return -1 


GO