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

,

This script will create scripts for every Table.
This makes it easy to check them in to VSS.

Usage :
DMOScriptTables 'Databasename','Directoryname'


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

Rate

Share

Share

Rate