home
Script database objects from tsql using sql-dmo
Author Nigel Rivett
This is for an uncontrolled environment where developers are allowed to change SPs without change control.
I would suggest to run the procedure every night.
Create a Database called Admin on the server to be scripted or a central server and place the SPs in it (and the dts package).
Schedule Admin..s_ScriptAllDatabases to run each night with the appropriate parameters.
s_ScriptAllDatabases includes a database parameter to enable scripting of a single database, dts packages or jobs.
These procedures will script the fllowing objects in each database on a server via SQL DMO
Stored Procedures
User Defined Functions
Tables
Views
Indexes
Triggers
Defaults
Rules
Jobs
dts package properties (via a dts package activexscript task)
To script DTS packages add the package indicated at
ScriptDTSProperties
Instances will be scripted int the directory <path>svrname^instancename\ for instance svrname\instancename
The directory will be creted in <path. as for default instances.
You will need to create a shared directory for the output (the subdirectories will be created.
Also a work directory is required - I would advise to place it local to the scripting server if possible.
After the script is run for the first time Add all the files to SourceSafe from the root directory
(Add, Recursive, Check out immediately).
After this for future script runs
I prefer to do this
Show differences, Show files that are only in the to location, Show files that are different in both places
check in / add these files keeping them checked out after checking all changes that have been made.
If you don't want to check changes
Check in, recursive, keep checked out
(The default options will not create entries for unchanged files)
Show differences, Show files that are only in the to location
Add these files keeping them checked out
To script a server run s_ScriptAllDatabases as indicated in the comment.
To script a remote server it must be added as a linked server to the scripting server.
Future enhancements
Allow spaces in work directory path
Automatically update SourceSafe with the results
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_ScriptAllDatabases]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ScriptAllDatabases]
GO
CREATE procedure s_ScriptAllDatabases
@SourceUID varchar(128) , -- null for trusted connection
@SourcePWD varchar(128) ,
@OutFilePath varchar(256) , -- Root path - will add directory for object types
@OutFileName varchar(128) , -- null for separate file per object script
@WorkPath varchar(256) ,
@SourceSVR varchar(128) = null , -- to script remote server
@Database varchar(128) = null -- to script single database / jobs / DTS
as
/*
exec s_ScriptAllDatabases
@SourceUID = null ,
@SourcePWD = null ,
@OutFilePath = 'c:\a\' ,
@OutFileName = null ,
@WorkPath = 'c:\temp\' , -- no spaces
@SourceSVR = null
exec s_ScriptAllDatabases
@SourceUID = null ,
@SourcePWD = null ,
@OutFilePath = 'c:\a\' ,
@OutFileName = null ,
@WorkPath = 'c:\temp\' , -- no spaces
@SourceSVR = null ,
@Database = 'JOBS'
exec s_ScriptAllDatabases
@SourceUID = null ,
@SourcePWD = null ,
@OutFilePath = 'c:\a\' ,
@OutFileName = null ,
@WorkPath = 'c:\temp\' , -- no spaces
@SourceSVR = null ,
@Database = 'DTS'
exec s_ScriptAllDatabases
@SourceUID = null ,
@SourcePWD = null ,
@OutFilePath = 'c:\a\' ,
@OutFileName = null ,
@WorkPath = 'c:\temp\' , -- no spaces
@SourceSVR = null ,
@Database = 'mydb'
*/
declare @sql varchar(1000) ,
@cmd varchar(1000)
if @SourceSVR is null
begin
select @SourceSVR = @@servername
end
if right(@OutFilePath,1) <> '\'
begin
select @OutFilePath = @OutFilePath + '\'
end
if right(@WorkPath,1) <> '\'
begin
select @WorkPath = @WorkPath + '\'
end
select @OutFilePath = @OutFilePath + '"' + @SourceSVR + '"'
exec master..xp_cmdshell @cmd
select @OutFilePath = @OutFilePath + '\'
select @sql =
'select name
from [' + @SourceSVR + '].master.dbo.sysdatabases
where name <> ''tempdb'''
if @Database is not null
begin
select @sql = @sql + ' and name = ''' + @Database + ''''
end
create table #tblDatabases (name varchar(128))
insert #tblDatabases
(name)
exec (@sql)
declare @FilePath varchar(256)
declare @name varchar(128) ,
@maxname varchar(128)
select @name = '' ,
@maxname = max(name)
from #tblDatabases
while @name < @maxname
begin
select @name = min(name) from #tblDatabases where name > @name
select @FilePath = @OutFilePath + '"' + @name + '"'
-- output current database name
select CurrentDatabase = @name
-- create output directory - will fail if already exists but ...
select @cmd = 'mkdir ' + @FilePath
exec master..xp_cmdshell @cmd, no_output
exec s_ScriptAllObjectsInDatabase
@SourceDB = @name ,
@SourceUID = @SourceUID ,
@SourcePWD = @SourcePWD ,
@OutFilePath = @FilePath ,
@OutFileName = @OutFileName , -- null for separate file per object script
@WorkPath = @WorkPath ,
@SourceSVR = @SourceSVR
end
if coalesce(@Database, 'JOBS') = 'JOBS'
begin
select @FilePath = @OutFilePath + 'JOBS'
-- create output directory - will fail if already exists but ...
select @cmd = 'mkdir ' + @FilePath
exec master..xp_cmdshell @cmd, no_output
exec s_ScriptObjects
@SourceDB = 'msdb' ,
@SourceObject = null , -- null for all objects
@SourceUID = @SourceUID ,
@SourcePWD = @SourcePWD ,
@OutFilePath = @FilePath ,
@OutFileName = @OutFileName , -- null for separate file per object script
@ObjectType = 'JOBS' ,
@WorkPath = @WorkPath ,
@SourceSVR = @SourceSVR
end
if coalesce(@Database, 'DTS') = 'DTS'
begin
select @FilePath = @OutFilePath + 'DTS'
-- create output directory - will fail if already exists but ...
select @cmd = 'mkdir ' + @FilePath
exec master..xp_cmdshell @cmd, no_output
exec s_ScriptObjects
@SourceDB = 'msdb' ,
@SourceObject = null , -- null for all objects
@SourceUID = @SourceUID ,
@SourcePWD = @SourcePWD ,
@OutFilePath = @FilePath ,
@OutFileName = @OutFileName , -- null for separate file per object script
@ObjectType = 'DTS' ,
@WorkPath = @WorkPath ,
@SourceSVR = @SourceSVR
end
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_ScriptAllObjectsInDatabase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ScriptAllObjectsInDatabase]
GO
Create procedure s_ScriptAllObjectsInDatabase
@SourceDB varchar(128) ,
@SourceUID varchar(128) , -- null for trusted connection
@SourcePWD varchar(128) ,
@OutFilePath varchar(256) , -- Root path - will add directory for object types
@OutFileName varchar(128) , -- null for separate file per object script
@WorkPath varchar(256) ,
@SourceSVR varchar(128)
as
if right(@OutFilePath,1) <> '\'
begin
select @OutFilePath = @OutFilePath + '\'
end
if right(@WorkPath,1) <> '\'
begin
select @WorkPath = @WorkPath + '\'
end
set nocount on
declare @tblObjectType table (ObjectType varchar(50))
insert @tblObjectType select 'PROCEDURES'
insert @tblObjectType select 'FUNCTIONS'
insert @tblObjectType select 'TABLES'
insert @tblObjectType select 'VIEWS'
insert @tblObjectType select 'INDEXES'
insert @tblObjectType select 'TRIGGERS'
insert @tblObjectType select 'DEFAULTS'
insert @tblObjectType select 'RULES'
declare @FilePath varchar(256) ,
@cmd varchar(1000)
declare @ObjectType varchar(50) ,
@maxObjectType varchar(50)
select @ObjectType = '' ,
@maxObjectType = max(ObjectType)
from @tblObjectType
while @ObjectType < @maxObjectType
begin
select @ObjectType = min(ObjectType) from @tblObjectType where ObjectType > @ObjectType
select @FilePath = @OutFilePath + @ObjectType
-- create output directory - will fail if already exists but ...
select @cmd = 'mkdir ' + @FilePath
exec master..xp_cmdshell @cmd, no_output
exec s_ScriptObjects
@SourceDB = @SourceDB ,
@SourceObject = null ,
@SourceUID = @SourceUID ,
@SourcePWD = @SourcePWD ,
@OutFilePath = @FilePath ,
@OutFileName = null , -- null for separate file per object script
@ObjectType = @ObjectType ,
@WorkPath = @WorkPath ,
@SourceSVR = @SourceSVR
end
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_ScriptObjects]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ScriptObjects]
GO
CREATE procedure s_ScriptObjects
@SourceDB varchar(128) ,
@SourceObject varchar(128) , -- null for all objects
@SourceUID varchar(128) , -- null for trusted connection
@SourcePWD varchar(128) ,
@OutFilePath varchar(256) ,
@OutFileName varchar(128) , -- null for separate file per object script
@ObjectType varchar(50) , -- PROCS, FUNCTIONS, TABLES, VIEWS, INDEXES
@WorkPath varchar(256) ,
@SourceSVR varchar(128)
as
/*
exec s_ScriptObjects
@SourceDB = 'TradarBe' ,
@SourceObject = 'tbl_CQS_Pricing_BloombergData' , -- null for all objects
@SourceUID = null , -- null for trusted connection
@SourcePWD = null ,
@OutFilePath = 'c:\a\' ,
@OutFileName = null , -- null for separate file per object script
@ObjectType = 'TABLES' , -- PROCS, FUNCTIONS, TABLES, VIEWS, INDEXES
@WorkPath = 'c:\temp\' ,
@SourceSVR = 'SVR01'
*/
set nocount on
declare @ScriptType int ,
@FileName varchar(256) ,
@tmpFileName varchar(256) ,
@buffer varchar(8000) ,
@Collection varchar(128) ,
@id int ,
@name varchar(128) ,
@subname varchar(128)
declare @context varchar(255) ,
@sql varchar(1000) ,
@rc int
if right(@OutFilePath,1) <> '\'
begin
select @OutFilePath = @OutFilePath + '\'
end
if right(@WorkPath,1) <> '\'
begin
select @WorkPath = @WorkPath + '\'
end
select @SourceDB = replace(replace(@SourceDB,'[',''),'[','')
select @ScriptType = 4 | 1 | 64 ,
@FileName = @OutFilePath + @OutFileName ,
@tmpFileName = @WorkPath + 'ScriptTmp.txt'
declare @objServer int ,
@objTransfer int ,
@strResult varchar(255) ,
@strCommand varchar(255)
-- get objects to script and object type
create table #Objects (name varchar(128), subname varchar(128) default null, id int identity(1,1))
if @SourceObject is not null
begin
insert #Objects
(name)
select @SourceObject
end
if @ObjectType = 'TABLES'
begin
if @SourceObject is null
begin
select @sql = 'select TABLE_NAME, null '
select @sql = @sql + 'from [' + @SourceDB + '].INFORMATION_SCHEMA.TABLES '
select @sql = @sql + 'where TABLE_TYPE = ''BASE TABLE'''
end
select @Collection = 'tables'
end
else if @ObjectType in ('PROCS', 'PROCEDURES')
begin
if @SourceObject is null
begin
select @sql = 'select ROUTINE_NAME, null '
select @sql = @sql + 'from [' + @SourceDB + '].INFORMATION_SCHEMA.ROUTINES '
select @sql = @sql + 'where ROUTINE_TYPE = ''PROCEDURE'''
end
select @Collection = 'storedprocedures'
end
else if @ObjectType = 'FUNCTIONS'
begin
if @SourceObject is null
begin
select @sql = 'select ROUTINE_NAME, null '
select @sql = @sql + 'from [' + @SourceDB + '].INFORMATION_SCHEMA.ROUTINES '
select @sql = @sql + 'where ROUTINE_TYPE = ''FUNCTION'''
end
select @Collection = 'userdefinedfunctions'
end
else if @ObjectType = 'VIEWS'
begin
if @SourceObject is null
begin
select @sql = 'select TABLE_NAME, null '
select @sql = @sql + 'from [' + @SourceDB + '].INFORMATION_SCHEMA.VIEWS '
select @sql = @sql + 'where TABLE_NAME not like ''sys%'''
end
select @Collection = 'views'
end
else if @ObjectType = 'INDEXES'
begin
if @SourceObject is null
begin
select @sql = 'select o.name, i.name '
select @sql = @sql + 'from [' + @SourceDB + ']..sysobjects o, [' + @SourceDB + ']..sysindexes i '
select @sql = @sql + 'where o.type = ''U'' '
select @sql = @sql + 'and i.id = o.id and i.indid <> 0 '
select @sql = @sql + 'and i.name not like ''_WA_%'''
select @sql = @sql + 'and o.name not like ''dtprop%'''
select @sql = @sql + 'and i.name not in (select name from [' + @SourceDB + ']..sysobjects)'
end
select @Collection = 'tables'
end
else if @ObjectType = 'TRIGGERS'
begin
if @SourceObject is null
begin
select @sql = 'select o2.name, o.name '
select @sql = @sql + 'from [' + @SourceDB + ']..sysobjects o, [' + @SourceDB + ']..sysobjects o2 '
select @sql = @sql + 'where o.xtype = ''TR'' '
select @sql = @sql + 'and o.parent_obj = o2.id '
end
select @Collection = 'tables'
end
else if @ObjectType = 'DEFAULTS'
begin
if @SourceObject is null
begin
select @sql = 'select o.name, null '
select @sql = @sql + 'from [' + @SourceDB + ']..sysobjects o '
select @sql = @sql + 'where o.type = ''D'' and o.parent_obj = ''0'''
end
select @Collection = 'Defaults'
end
else if @ObjectType = 'RULES'
begin
if @SourceObject is null
begin
select @sql = 'select o.name, null '
select @sql = @sql + 'from [' + @SourceDB + ']..sysobjects o '
select @sql = @sql + 'where type = ''R'''
end
select @Collection = 'Rules'
end
else if @ObjectType = 'JOBS'
begin
if @SourceObject is null
begin
select @sql = 'select j.name, null '
select @sql = @sql + 'from msdb..sysjobs j '
end
select @Collection = 'jobs'
end
else if @ObjectType = 'DTS'
begin
select @sql = 'dtsrun /NScript_DTS_Packages /S(local) /E '
+ '/A"ServerName":8="' + @SourceSVR + '" '
+ '/A"Path":8="' + @OutFilePath + '" '
+ '/A"UserName":8="' + coalesce(@SourceUID,'') + '" '
+ '/A"Password":8="' + coalesce(@SourcePWD,'') + '" '
exec master..xp_cmdshell @sql
return
end
else
begin
select 'invalid @ObjectType'
return
end
if @SourceSVR <> @@servername
begin
select @sql = replace(@sql,'''','''''')
insert #Objects (name, subname) exec ('select * from openquery(' + @SourceSVR + ',''' + @sql + ''')')
end
else
begin
insert #Objects (name, subname) exec (@sql)
end
-- create empty output file
if @OutFileName is not null
begin
select @sql = 'echo. > ' + @FileName
exec master..xp_cmdshell @sql
end
-- prepare scripting object
select @context = 'create dmo object'
exec @rc = sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
if @rc <> 0 or @@error <> 0 goto ErrorHnd
if @SourceUID is null
begin
select @context = 'set integrated security ' + @SourceSVR
exec @rc = sp_OASetProperty @objServer, LoginSecure, 1
if @rc <> 0 or @@error <> 0 goto ErrorHnd
end
select @context = 'connect to server ' + @SourceSVR
exec @rc = sp_OAMethod @objServer , 'Connect', NULL, @SourceSVR , @SourceUID , @SourcePWD
if @rc <> 0 or @@error <> 0 goto ErrorHnd
select @context = 'scripting'
-- Script all the objects
select @id = 0
while exists (select * from #Objects where id > @id)
begin
select @id = min(id) from #Objects where id > @id
select @name = name, @subname = subname from #Objects where id = @id
if @OutFileName is null
begin
select @FileName = @OutFilePath + 'dbo."' + @name + coalesce('[' + @subname + ']','') + '.sql"'
select @sql = 'echo. > ' + @FileName
exec master..xp_cmdshell @sql
end
--select @sql = 'echo print ''Create = dbo.[' + @name + ']'+ coalesce('[' + @subname + ']','') + ''' >> ' + @FileName
--exec master..xp_cmdshell @sql
if @ObjectType = 'INDEXES'
begin
Set @sql = 'databases("' + @SourceDB + '").' + @Collection + '("' + @name + '").indexes("' + @subname + '").script'
end
else if @ObjectType = 'TRIGGERS'
begin
Set @sql = 'databases("' + @SourceDB + '").' + @Collection + '("' + @name + '").triggers("' + @subname + '").script'
end
else if @ObjectType = 'JOBS'
begin
Set @sql = 'Jobserver.Jobs("' + @name + '").Script'
end
else
begin
Set @sql = 'databases("' + @SourceDB + '").' + @Collection + '("' + @name + '").script'
end
exec @rc = sp_OAMethod @objServer, @sql , @buffer OUTPUT, @ScriptType , @tmpFileName
select @sql = 'type ' + @tmpFileName + ' >> ' + @FileName
exec master..xp_cmdshell @sql
end
-- delete tmp file
select @sql = 'del ' + @tmpFileName
exec master..xp_cmdshell @sql, no_output
-- clear up dmo
exec @rc = sp_OAMethod @objServer, 'Disconnect'
if @rc <> 0 or @@error <> 0 goto ErrorHnd
exec @rc = sp_OADestroy @objServer
if @rc <> 0 or @@error <> 0 goto ErrorHnd
-- clear up temp table
drop table #Objects
return
ErrorHnd:
select 'fail', @context
GO
home