Generate SQL Script...
-
I wrote a script a couple years ago that takes a set of tables and does the same thing. Mine builds out the database using the same filegroups and file sizes of the origional one scripted from, handles primary keys, foreign keys, unique constraints, indexes, and stored procedures as well. I use a table called object_matrix to determine required tables and procs, as I use it to build out enviroments. I have a script for generating the replication as well, starting with the distributor database, creating articles and subscriptions, and finally kicking off the snapshots to populate the tables.
I am happy to send it to anyone who might benefit from it and high enough level to be able to work with it without my intervention, but it's too complicated to simply post without a document or something to go with it on how to modify it to your uses. (I might mention that while very functional, it's ugly, as I have never gone back and cleaned it up. Didn't need to for my uses.....
) -
Well it is not completely mine but here is a nice sproc that will script everything in a DB to a file......
CREATE procedure dbo.dmoScriptDatabase
@pDatabaseName varchar(255),
@workingFolder varchar(255),
@pInstanceName varchar(30) = null
as
/**********************************************************************************
Script database collections using DMO object model granularity. By default, scripts
are placed in the dbChangeControl working folders for this database on this SQL
Server instance. By providing an alternate valid path to the cloaked tempFolder parameter
the scripts can be placed in another location. Additionally, by providing an instance
name to the cloaked Instance Parameter, another instance on the local machine can be
scripted provided it is established in the metadata tables.
Notes. Use with dmoScriptTables to get table collections. The specified database on
the local server must be a member of a healthy project. If an attempt is made to
script to the working folders of a checked in project, the scripting will fail
because the existing files are read only. Procedure will allow
***********************************************************************************/
-- 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,
@hrhex char(10),
@OleErrorSource varchar(255),
@OleErrorDescription varchar(1000),
@scriptType int,
@databaseScriptType int,
@procedureName sysname,
@Processflowerror varchar(255),
@pTempFolder varchar(255),
-- defaults
@defaultCount int,
@curDefaultNbr int,
@defaultName varchar(255),
-- full text catalog
@catalogCount int,
@curCatalogNbr int,
@catalogName varchar(255),
-- roles
@roleCount int,
@curRoleNbr int,
@roleName varchar(255),
@isFixedRole bit,
-- rules
@ruleCount int,
@curRuleNbr int,
@ruleName varchar(255),
-- stored procedures
@storedProcedureCount int,
@curStoredProcedureNbr int,
@storedProcedureName varchar(255),
@isSystemStoredProcedure bit,
@procedureScriptType int,
-- user data types
@dataTypeCount int,
@curDataTypeNbr int,
@dataTypeName varchar(255),
-- user functions
@functionCount int,
@curFunctionNbr int,
@functionName varchar(255),
-- users
@userCount int,
@curUserNbr int,
@userName varchar(255),
@loginName varchar(255),
@loginScriptType int,
@userScriptType int,
-- views
@viewCount int,
@curViewNbr int,
@viewName varchar(255),
@isSystemView bit,
@viewScriptType int
-- 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)
-- file system project working folder must exist
set @cmd = 'dir ' + '"'+@workingFolder+'"'
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0
begin
set @cmd = 'MD ' + @workingFolder
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0
begin
raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
set @Processflowerror = 'Could not create working Directory :' + @CMD
goto ErrorHandler
end
else
begin
insert @ActivityLog (activity) values ('Working directory sucessfully created')
end
end
else
begin
insert @ActivityLog (activity) values ('Target directory sucessfully located')
end
-- The Target Direcory does Exist now kill all the files in it
set @cmd = 'RMDIR ' + '"'+@workingFolder + '\"' + ' /q /s'
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0
begin
raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
set @Processflowerror = 'Could not delete Target Directory :' + @CMD
end
else
begin
insert @ActivityLog (activity) values ('Target directory sucessfully deleted')
end
-- Create the Dir structure
set @cmd = 'MD ' + '"' + @prefix_fil + '"'
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0
begin
raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
set @Processflowerror = 'Could not create filegoups Directory :' + @CMD
goto ErrorHandler
end
else
begin
insert @ActivityLog (activity) values ('Filegroup directory sucessfully created')
end
set @cmd = 'MD ' +'"' + @prefix_tab + '"'
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0
begin
raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
set @Processflowerror = 'Could not create Table Directory :' + @CMD
goto ErrorHandler
end
else
begin
insert @ActivityLog (activity) values ('Table directory sucessfully created')
end
set @cmd = 'MD ' + '"' + @prefix_cns + '"'
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0
begin
raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
set @Processflowerror = 'Could not create Constraints Directory :' + @CMD
goto ErrorHandler
end
else
begin
insert @ActivityLog (activity) values ('Constraints directory sucessfully created')
end
set @cmd = 'MD ' + '"' + @prefix_viw + '"'
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0
begin
raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
set @Processflowerror = 'Could not create Views Directory :' + @CMD
goto ErrorHandler
end
else
begin
insert @ActivityLog (activity) values ('View directory sucessfully created')
end
set @cmd = 'MD ' + '"' + @prefix_trg + '"'
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0
begin
raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
set @Processflowerror = 'Could not create Trigger Directory :' + @CMD
goto ErrorHandler
end
else
begin
insert @ActivityLog (activity) values ('Trigger directory sucessfully created')
end
set @cmd = 'MD ' + '"' + @prefix_rul + '"'
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0
begin
raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
set @Processflowerror = 'Could not create Rules Directory :' + @CMD
goto ErrorHandler
end
else
begin
insert @ActivityLog (activity) values ('Rules directory sucessfully created')
end
set @cmd = 'MD ' + '"' + @prefix_ind + '"'
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0
begin
raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
set @Processflowerror = 'Could not create Index Directory :' + @CMD
goto ErrorHandler
end
else
begin
insert @ActivityLog (activity) values ('Index directory sucessfully created')
end
set @cmd = 'MD ' + '"' + @prefix_prc + '"'
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0
begin
raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
set @Processflowerror = 'Could not create Stored Procedure Directory :' + @CMD
goto ErrorHandler
end
else
begin
insert @ActivityLog (activity) values ('Stored Procedure directory sucessfully created')
end
set @cmd = 'MD ' + '"' + @prefix_udf + '"'
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0
begin
raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
set @Processflowerror = 'Could not create User defined functions Directory :' + @CMD
goto ErrorHandler
end
else
begin
insert @ActivityLog (activity) values ('User defined functions Procedure directory sucessfully created')
end
set @cmd = 'MD ' + '"' + @prefix_def + '"'
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0
begin
raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
set @Processflowerror = 'Could not create Defaults Directory :' + @CMD
goto ErrorHandler
end
else
begin
insert @ActivityLog (activity) values ('Defaults directory sucessfully created')
end
set @cmd = 'MD ' + '"' + @prefix_ftc + '"'
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0
begin
raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
set @Processflowerror = 'Could not create Full Text Catalog Directory :' + @CMD
goto ErrorHandler
end
else
begin
insert @ActivityLog (activity) values ('Full Text Catalog directory sucessfully created')
end
set @cmd = 'MD ' + '"' + @prefix_rol + '"'
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0
begin
raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
set @Processflowerror = 'Could not create Roles Directory :' + @CMD
goto ErrorHandler
end
else
begin
insert @ActivityLog (activity) values ('Roles Catalog directory sucessfully created')
end
set @cmd = 'MD ' + '"' + @prefix_udt + '"'
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0
begin
raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
set @Processflowerror = 'Could not create User defined Datatypes Directory :' + @CMD
goto ErrorHandler
end
else
begin
insert @ActivityLog (activity) values ('User defined Datatypes directory sucessfully created')
end
set @cmd = 'MD ' + '"' + @prefix_usr + '"'
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0
begin
raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
set @Processflowerror = 'Could not create User Directory :' + @CMD
goto ErrorHandler
end
else
begin
insert @ActivityLog (activity) values ('User directory sucessfully created')
end
set @path = @workingFolder
-- new file, script drop object and create object
set @scriptType = 1 -- drop
+ 4 -- primary object
+ 64 -- to file only
+ 4096 -- if not exists
+ 262144 -- owner qualify
-- database script type - non destructive
set @databaseScriptType = 4 -- primary object
+ 64 -- to file only
+ 4096 -- if not exists
-- new file, script create object only
set @loginScriptType = 4 -- primary object
+ 64 -- to file only
+ 4096 -- if not exists
-- new file, script create object only
set @userScriptType = 1 -- drop
+ 4 -- primary object
+ 64 -- to file only
+ 256 -- append (login script will create)
+ 4096 -- if not exists
-- script drop object, create object, and permissions
set @procedureScriptType = 1 -- drop
+ 2 -- object permissions
+ 4 -- primary object
+ 32 -- database permissions
+ 64 -- to file only
+ 4096 -- if not exists
+ 262144 -- owner qualify
-- script drop object, create object, and permissions
set @viewScriptType = 1 -- drop
+ 2 -- object permissions
+ 4 -- primary object
+ 32 -- database permissions
+ 64 -- to file only
+ 4096 -- if not exists
+ 262144 -- owner qualify
-- open an in-process COM/DMO connection to this server
exec @hr = master.dbo.sp_OACreate 'SQLDMO.SQLServer',
@dmoServer OUT
if @hr <> 0
goto ErrorHandler
-- set the security context to integrated
exec @hr = master.dbo.sp_OASetProperty @dmoServer,
'loginSecure',
1 -- NT Authentication
if @hr <> 0
goto ErrorHandler
-- connect to the specified server
exec @hr = master.dbo.sp_OAMethod @dmoServer,
'Connect',
NULL,
@@servername
if @hr <> 0
goto ErrorHandler
-- script each object to a separate file
-- database
select @dmoMethod = 'Databases("' + @pDatabaseName + '").Script'
select @scriptFile = @prefix_fil +@pdatabasename+'.Db'
exec @hr = master.dbo.sp_OAMethod @dmoServer,
@dmoMethod,
NULL,
@databaseScriptType,
@scriptFile
if @hr <> 0 goto ErrorHandler
insert @ActivityLog (activity) values ('Database Successfully scripted')
-- defaults
select @dmoProperty = 'Databases("' + @pDatabaseName + '").Defaults.Count'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,@dmoProperty,@defaultCount OUT
if @hr <> 0 goto ErrorHandler
set @curDefaultNbr = 1
while @curDefaultNbr <= @defaultCount
begin
-- get the name
select @dmoProperty = 'Databases("' + @pDatabaseName + '").Defaults.Item(' + cast(@curDefaultNbr as varchar(10))+ ').Name'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,@dmoProperty,@defaultName OUT
if @hr <> 0 goto ErrorHandler
select @dmoMethod = 'Databases("' + @pDatabaseName + '").Defaults("' + @defaultName + '").Script'
select @scriptFile = @prefix_def + @defaultName + '.def'
exec @hr = master.dbo.sp_OAMethod @dmoServer,
@dmoMethod,
NULL,
@scriptType,
@scriptFile
if @hr <> 0 goto ErrorHandler
select @curDefaultNbr = @curDefaultNbr + 1
end
insert @ActivityLog (activity) values ('Defaults Successfully scripted')
-- full text catalogs
select @dmoProperty = 'Databases("' + @pDatabaseName + '").FullTextCatalogs.Count'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@catalogCount OUT
if @hr <> 0 goto ErrorHandler
set @curCatalogNbr = 1
while @curCatalogNbr <= @catalogCount
begin
-- get the name
select @dmoProperty = 'Databases("' + @pDatabaseName + '").FullTextCatalogs.Item(' + cast(@curCatalogNbr as varchar(10))+ ').Name'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@catalogName OUT
if @hr <> 0 goto ErrorHandler
select @dmoMethod = 'Databases("' + @pDatabaseName + '").FullTextCatalogs("' + @catalogName + '").Script'
select @scriptFile = @prefix_def + @catalogName + '.cat'
exec @hr = master.dbo.sp_OAMethod @dmoServer,
@dmoMethod,
NULL,
@scriptType,
@scriptFile
if @hr <> 0 goto ErrorHandler
select @curCatalogNbr = @curCatalogNbr + 1
end
insert @ActivityLog (activity) values ('FullTextCatalogs Successfully scripted')
-- roles
select @dmoProperty = 'Databases("' + @pDatabaseName + '").DatabaseRoles.Count'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@roleCount OUT
if @hr <> 0 goto ErrorHandler
set @curRoleNbr = 1
while @curRoleNbr <= @RoleCount
begin
-- fixed roles cannot be removed so don't try to script
select @dmoProperty = 'Databases("' + @pDatabaseName + '").DatabaseRoles.Item(' + cast(@curRoleNbr as varchar(10)) + ').IsFixedRole'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@isFixedRole OUT
if @hr <> 0 goto ErrorHandler
-- get the name
select @dmoProperty = 'Databases("' + @pDatabaseName + '").DatabaseRoles.Item(' + cast(@curRoleNbr as varchar(10)) + ').Name'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@roleName OUT
if @hr <> 0 goto ErrorHandler
if @isFixedRole = 0 and @roleName <> 'Public'
begin
select @dmoMethod = 'Databases("' + @pDatabaseName + '").DatabaseRoles("' + @roleName+ '").Script'
select @scriptFile = @prefix_rol + @roleName + '.rol'
exec @hr = master.dbo.sp_OAMethod @dmoServer,
@dmoMethod,
NULL,
@scriptType,
@scriptFile
if @hr <> 0 goto ErrorHandler
end
select @curRoleNbr = @curRoleNbr + 1
end
insert @ActivityLog (activity) values ('Roles Successfully scripted')
-- rules
select @dmoProperty = 'Databases("' + @pDatabaseName + '").Rules.Count'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,@dmoProperty,@ruleCount OUT
if @hr <> 0 goto ErrorHandler
set @curRuleNbr = 1
while @curRuleNbr <= @RuleCount
begin
-- get the name
select @dmoProperty = 'Databases("' + @pDatabaseName + '").Rules.Item(' + cast(@curRuleNbr as varchar(10)) + ').Name'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@ruleName OUT
if @hr <> 0 goto ErrorHandler
select @dmoMethod = 'Databases("' + @pDatabaseName + '").Rules("' + @ruleName + '").Script'
select @scriptFile = @prefix_rul+@ruleName + '.rul'
exec @hr = master.dbo.sp_OAMethod @dmoServer,
@dmoMethod,
NULL,
@scriptType,
@scriptFile
if @hr <> 0 goto ErrorHandler
select @curRuleNbr = @curRuleNbr + 1
end
insert @ActivityLog (activity) values ('Rules Successfully scripted')
-- stored procedures
select @dmoProperty = 'Databases("' + @pDatabaseName + '").StoredProcedures.Count'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,@dmoProperty,@storedProcedureCount OUT
if @hr <> 0 goto ErrorHandler
set @curStoredProcedureNbr = 1
while @curStoredProcedureNbr <= @StoredProcedureCount
begin
select @dmoProperty = 'Databases("' + @pDatabaseName + '").StoredProcedures.Item(' + cast(@curStoredProcedureNbr as varchar(5)) + ').SystemObject'
exec @hr = master..sp_OAGetProperty @dmoServer,
@dmoProperty,
@isSystemStoredProcedure OUT
if @hr <> 0 goto ErrorHandler
if @isSystemStoredProcedure = 0
begin
-- get the name
select @dmoProperty = 'Databases("' + @pDatabaseName + '").StoredProcedures.Item(' + cast(@curStoredProcedureNbr as varchar(10))+ ').Name'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,@dmoProperty,@storedProcedureName OUT
if @hr <> 0 goto ErrorHandler
select @dmoMethod = 'Databases("'
+ @pDatabaseName
+ '").StoredProcedures("'
+ @StoredProcedureName
+ '").Script'
select @scriptFile = @prefix_prc+ @StoredProcedureName + '.prc'
exec @hr = master.dbo.sp_OAMethod @dmoServer,
@dmoMethod,
NULL,
@ProcedureScriptType,
@scriptFile
if @hr <> 0 goto ErrorHandler
end
select @curStoredProcedureNbr = @curStoredProcedureNbr + 1
end
insert @ActivityLog (activity) values ('Stored procedures Successfully scripted')
-- user data types
select @dmoProperty = 'Databases("' + @pDatabaseName + '").UserDefinedDataTypes.Count'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,@dmoProperty,@dataTypeCount OUT
if @hr <> 0 goto ErrorHandler
set @curDataTypeNbr = 1
while @curDataTypeNbr <= @dataTypeCount
begin
-- get the name
select @dmoProperty = 'Databases("' + @pDatabaseName + '").UserDefinedDataTypes.Item(' + cast(@curDataTypeNbr as varchar(10))+ ').Name'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,@dmoProperty,@dataTypeName OUT
if @hr <> 0 goto ErrorHandler
select @dmoMethod = 'Databases("'
+ @pDatabaseName
+ '").UserDefinedDataTypes("'
+ @dataTypeName
+ '").Script'
select @scriptFile = @prefix_udt + @dataTypeName + '.udt'
exec @hr = master.dbo.sp_OAMethod @dmoServer,
@dmoMethod,
NULL,
@scriptType,
@scriptFile
if @hr <> 0 goto ErrorHandler
select @curDataTypeNbr = @curDataTypeNbr + 1
end
insert @ActivityLog (activity) values ('User defined datatypes Successfully scripted')
-- user functions (sql2000 or greater)
select @dmoProperty = 'Databases("'
+ @pDatabaseName
+ '").UserDefinedFunctions.Count'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
@dmoProperty,
@functionCount OUT
if @hr <> 0
goto ErrorHandler
set @curFunctionNbr = 1
while @curFunctionNbr <= @functionCount
begin
-- get the name
select @dmoProperty = 'Databases("' + @pDatabaseName + '").UserDefinedFunctions.Item(' + cast(@curFunctionNbr as varchar(10))+ ').Name'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,@dmoProperty,@functionName OUT
if @hr <> 0 goto ErrorHandler
select @dmoMethod = 'Databases("'
+ @pDatabaseName
+ '").UserDefinedFunctions("'
+ @functionName
+ '").Script'
select @scriptFile = @prefix_udf+ @functionName + '.udf'
exec @hr = master.dbo.sp_OAMethod @dmoServer,@dmoMethod,NULL,@scriptType,@scriptFile
if @hr <> 0 goto ErrorHandler
select @curFunctionNbr = @curFunctionNbr + 1
end
insert @ActivityLog (activity) values ('User defined functions Successfully scripted')
-- users
select @dmoProperty = 'Databases("' + @pDatabaseName + '").Users.Count'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,@dmoProperty,@userCount OUT
if @hr <> 0 goto ErrorHandler
set @curUserNbr = 1
while @curUserNbr <= @userCount
begin
-- get the name
select @dmoProperty = 'Databases("' + @pDatabaseName + '").Users.Item(' + cast(@curUserNbr as varchar(10)) + ').Name'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,@dmoProperty,@userName OUT
if @hr <> 0 goto ErrorHandler
if @userName <> 'guest'
begin
-- get the login name
select @dmoProperty = 'Databases("'+ @pDatabaseName + '").Users.Item(' + cast(@curUserNbr as varchar(10))+ ').Login'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer, @dmoProperty, @loginName OUT
if @hr <> 0 goto ErrorHandler
-- start the file with the login script but do not drop existing
if @loginName is not null
begin
select @scriptFile = @prefix_usr + replace(@userName ,'\','~') + '.usr'
select @dmoMethod = 'Logins("'
+ @loginName
+ '").Script'
exec @hr = master.dbo.sp_OAMethod
@dmoServer,
@dmoMethod,
NULL,
@loginScriptType,
@scriptFile
if @hr <> 0 goto ErrorHandler
end
-- append the user script
select @dmoMethod = 'Databases("'
+ @pDatabaseName
+ '").Users("'
+ @userName
+ '").Script'
select @scriptFile = @prefix_usr+ replace(@userName ,'\','~')+ '.usr'
exec @hr = master.dbo.sp_OAMethod @dmoServer,@dmoMethod,NULL,@userScriptType,@scriptFile
if @hr <> 0 goto ErrorHandler
end
select @curUserNbr = @curUserNbr + 1
end
insert @ActivityLog (activity) values ('Users Successfully scripted')
-- views
select @dmoProperty = 'Databases("' + @pDatabaseName + '").Views.Count'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,@dmoProperty,@viewCount OUT
if @hr <> 0 goto ErrorHandler
set @curViEwNbr = 1
while @curViewNbr <= @viewCount
begin
select @dmoProperty = 'Databases("' + @pDatabaseName + '").Views.Item(' + cast(@curViewNbr as varchar(5)) + ').SystemObject'
exec @hr = master..sp_OAGetProperty @dmoServer,@dmoProperty,@isSystemView OUT
if @hr <> 0 goto ErrorHandler
if @isSystemView = 0
begin
-- get the name
select @dmoProperty = 'Databases("' + @pDatabaseName + '").Views.Item(' + cast(@curViewNbr as varchar(10)) + ').Name'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,@dmoProperty,@viewName OUT
if @hr <> 0 goto ErrorHandler
select @dmoMethod = 'Databases("'
+ @pDatabaseName
+ '").Views("'
+ @viewName
+ '").Script'
select @scriptFile = @prefix_viw + @viewName + '.viw'
exec @hr = master.dbo.sp_OAMethod @dmoServer,
@dmoMethod,
NULL,
@viewScriptType,
@scriptFile
if @hr <> 0 goto ErrorHandler
end
select @curViewNbr = @curViewNbr + 1
end
insert @ActivityLog (activity) values ('Views Successfully scripted')
-- 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
-
You will need the tables to bes cripted as well......
Here is the script.....
create procedure dbo.dmoScriptTables
@pDatabaseName varchar(255),
@workingfolder varchar(255) = null,
@pInstanceName varchar(30) = null
as
/**********************************************************************************
Script table collections using DMO object model granularity. By default, scripts
are placed in the dbChangeControl working folders of specified database on this SQL
Server instance. By providing an alternate valid path to the cloaked tempFolder parameter
the scripts can be placed in another location. Additionally, by providing an instance
name to the cloaked Instance Parameter, another instance on the local machine can be
scripted provided it is established in the metadata tables.
Notes. Use with dmoScriptDatabse to get database collections. The specified database
on the local server must be a member of a healthy project. If an attempt is made to
script to the working folders of a checked in project, the scripting will fail
because the existing files are read only.
***********************************************************************************/
-- 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
-
Very nice......and so much cleaner than the one I use....If I could pass it a list of tables, and have it generate only the needed scripts (Related directly to the tables passed), I would change over to it today. Looks easy enough to modify though....Kudos to well0549.....even if you are still using dmo....
)
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply