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'