This script will create scripts for every Table.
This makes it easy to check them in to VSS.
Usage :
DMOScriptTables 'Databasename','Directoryname'
2007-04-16 (first published: 2007-03-20)
13,862 reads
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
Related content
Full server documentation
This script is intended for SQL Server professionals that needs to have a quick snapshot from an environment with all relevant information about the databases. It's a compilation from multiple scripts, and works in SQL Server 2000.I think that works on 7.0 too, but may have some compilation issues.Don´t forget to see the section MANUAL […]
2003-04-28
4,683 reads
SP to show information about log in databases
It calculates log free space in mb and in percent for all databases, and also show the quantity of different objects for one databases.
2002-02-02
2,723 reads
List Where SQL Server Files
This SP checks the path where SQL Server is installed, and then lists all the .mdf and .ldf files. Also list the quantity of tables, views, sp, xp and users on the given database.
2001-09-10
2,746 reads
Automatic Backups
Creates the backup devices where sql server is installed, creates an error message above 50001, an alert, and an adittional job in case the scripts fails. In the first time backups all databases and then backups the logs until two days past from the last full backup. Then backups the databases again and so on. […]
2001-09-07
1,212 reads