How do I script all the objects from all databases on a server and record changes

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 an place the SPs in it.
Schedule Admin..s_ScriptAllDatabases to run each night.

These procedures will script the fllowing objects in each database on a server via SQL DMO
Stored Procedures
User Defined Functions
Tables
Views
Indexes

You will need to create a schared directory for the output (the subdirectories will be created.
Also a work directory is required - suggest to put it local to the server.

After the script is run for the first time Add all the files to SourceSafe from the root directory
(Add, Recusive, Check out immediately).
After following script runs
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
check in these files keeping them checked out

To script a server run s_ScriptAllDatabases as indicated in the comment.

Future enhancements
Allow spaces in work directory path
Allow remote scripting of a server
Automatically update SourceSafe with the results


if exists (select * from 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)
as
/*
exec s_ScriptAllDatabases
@SourceUID = null ,
@SourcePWD = null ,
@OutFilePath = '\\mymachine\nigel\mphamrsql03\' ,
@OutFileName = null ,
@WorkPath = 'e:\nigel\' -- local to server - no spaces
*/

if right(@OutFilePath,1) <> '\'
begin
select @OutFilePath = @OutFilePath + '\'
end

if right(@WorkPath,1) <> '\'
begin
select @WorkPath = @WorkPath + '\'
end

declare @tblDatabases table (name varchar(128))
insert @tblDatabases
(name)
select name
from master..sysdatabases
where name <> 'tempdb'

declare @FilePath varchar(256) ,
@cmd varchar(1000)

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

exec s_ScriptAllObjectsInDatabase
@SourceDB = @name ,
@SourceUID = @SourceUID ,
@SourcePWD = @SourcePWD ,
@OutFilePath = @FilePath ,
@OutFileName = @OutFileName , -- null for separate file per object script
@WorkPath = @WorkPath
end
go

if exists (select * from 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)

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 'PROCS'
insert @tblObjectType select 'FUNCTIONS'
insert @tblObjectType select 'TABLES'
insert @tblObjectType select 'VIEWS'
insert @tblObjectType select 'INDEXES'

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

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
end
go

if exists (select * from 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)
as
set nocount on

declare @SourceSVR varchar(128) ,
@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

select @SourceSVR = @@servername

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), 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 = 'insert #Objects (name) '
select @sql = @sql + 'select TABLE_NAME '
select @sql = @sql + 'from [' + @SourceDB + '].INFORMATION_SCHEMA.TABLES '
select @sql = @sql + 'where TABLE_TYPE = 'BASE TABLE''
exec (@sql)
end
select @Collection = 'tables'
end
else if @ObjectType = 'PROCS'
begin
if @SourceObject is null
begin
select @sql = 'insert #Objects (name) '
select @sql = @sql + 'select ROUTINE_NAME '
select @sql = @sql + 'from [' + @SourceDB + '].INFORMATION_SCHEMA.ROUTINES '
select @sql = @sql + 'where ROUTINE_TYPE = 'PROCEDURE''
exec (@sql)
end
select @Collection = 'storedprocedures'
end
else if @ObjectType = 'FUNCTIONS'
begin
if @SourceObject is null
begin
select @sql = 'insert #Objects (name) '
select @sql = @sql + 'select ROUTINE_NAME '
select @sql = @sql + 'from [' + @SourceDB + '].INFORMATION_SCHEMA.ROUTINES '
select @sql = @sql + 'where ROUTINE_TYPE = 'FUNCTION''
exec (@sql)
end
select @Collection = 'userdefinedfunctions'
end
else if @ObjectType = 'VIEWS'
begin
if @SourceObject is null
begin
select @sql = 'insert #Objects (name) '
select @sql = @sql + 'select TABLE_NAME '
select @sql = @sql + 'from [' + @SourceDB + '].INFORMATION_SCHEMA.VIEWS '
select @sql = @sql + 'where TABLE_NAME not like 'sys%''
exec (@sql)
end
select @Collection = 'views'
end
else if @ObjectType = 'INDEXES'
begin
if @SourceObject is null
begin
select @sql = 'insert #Objects (name, subname) '
select @sql = @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)'
exec (@sql)
end
select @Collection = 'tables'
end
else
begin
select 'invalid @ObjectType'
return
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 + '"' + @name + coalesce('[' + @subname + ']',') + '.sql"'
select @sql = 'echo. > ' + @FileName
exec master..xp_cmdshell @sql
end
select @sql = 'echo print 'Create = [' + @name + ']'+ coalesce('[' + @subname + ']',') + '' >> ' + @FileName
exec master..xp_cmdshell @sql
if @ObjectType = 'INDEXES'
begin
Set @sql = 'databases("' + @SourceDB + '").' + @Collection + '("' + @name + '").indexes("' + @subname + '").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

-- 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