by
Shailesh Khanal
How many times you have been asked by a developer if you can
recover a stored procedure or a table structure because he/she made a change in
the existing environment. In a controlled database development environment, scripts for all
database objects including tables, views, triggers and stored procedures should
be maintained in a software version control systems such as SourceSafe. Unless
you do all your database coding in an environment like Visual Studio it is not
always easy to maintain database codes in SourceSafe. Most people create/edit
database objects from SQL Management Studio and changes may not be tracked.
SQL Server Management Studio provides a wizard to generate
script out of a database. It would be nice if we could automate the process to
generate and store the database script somewhere.
The following script utilizes SQL Server Publishing Wizard
to do just that. You can use it for one server or you can use it for all
servers in your environment. The server details are maintained in a table
called ServerNames, in a database called DBTracker.
USE [DBtracker]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ServerNames](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SERVERNAME] [varchar](255) NULL,
[DDL] [tinyint] NULL CONSTRAINT [DF_ServerNames_DDL] DEFAULT ((0))
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
A value of 1 in column Active indicates you want to generate
DDL for this server. You can create a SQL Agent job, paste the following script
and schedule it to run as you need.
SET NOCOUNT ON
DECLARE @ServerNames TABLE
(ID INT IDENTITY(1,1),
SERVERNAME VARCHAR(255))
DECLARE @ServerDBNames TABLE
(ID INT IDENTITY(1,1),
SERVERNAME VARCHAR(255),
DATABASENAME VARCHAR(255))
DECLARE @servername varchar(255)
DECLARE @DBName varchar(255)
DECLARE @sql varchar(4000)
DECLARE @RowNum tinyint
DECLARE @MaxRow tinyint
Declare @CMD varchar(2000)
Declare @SQLPubWizExe varchar(150)
Declare @FolderName varchar(150)
SET @RowNum = 1
INSERT INTO @ServerNames (Servername)
SELECT Servername
FROM DBTracker.dbo.ServerNames
WHERE Active=1 AND DDL =1
ORDER BY ID
SELECT @MaxRow = max(ID) from @ServerNames
–loop through it and generate script
WHILE @RowNum <= @MaxRow
BEGIN
SELECT @servername=servername from @ServerNames where ID = @RowNum
SET @sql = ‘SELECT ”’+@servername+”’,
DB.Name FROM OPENROWSET(”SQLNCLI”, ”Server=’ + @servername + ‘;Trusted_Connection=yes;”,’
SET @sql = @sql + ”’SELECT Name FROM sysdatabases
WHERE dbid > 4 ORDER BY Name”) AS DB;’
INSERT INTO @ServerDBNames (servername, databasename)
EXEC (@sql)
SET @RowNum = @RowNum + 1;
END
SELECT @MaxRow = max(ID) from @ServerDBNames
SET @SQLPubWizExe = ‘D:\”Program Files (x86)”\”Microsoft SQL Server”\90\Tools\Publishing\SQLPubWiz.Exe’
SET @FolderName = ‘F:\DDL_OUTPUT\’
WHILE @RowNum <= @MaxRow
BEGIN
SELECT @servername=servername,@DBName=Databasename from @ServerDBNames where ID = @RowNum
SELECT @CMD =’EXEC master..xp_cmdshell ”’ + @SQLPubWizExe + ‘ script -C “Data Source=’
+@ServerName+’;Initial Catalog=’+ @DBName + ‘;Integrated Security=SSPI;” ‘ +
@FolderName + REPLACE(@SERVERNAME,’,’,’_’)+’_’+@DBName+’_’+
REPLACE(convert(varchar(8),getdate(),112)+’_’+convert(varchar(8),getdate(),114),’:’,”)+
’.sql -schemaonly -f”,NO_OUTPUT’
–print @CMD
Exec (@CMD)
SET @RowNum = @RowNum + 1;
END
Modify the @SQLPubWizExe variable to point to the
SQLPubWiz.exe in your environment and modify @FolderName to point to the script
output location.
The job connects to each server using Windows
authentication, so make sure that the SQL Server Agent running this job has
access to all the servers listed in the ServerNames table.
Since this calls xp_cmdshell to run the Publication Wizard,
you will need to enable xp_cmdshell from SQL Configuration Manager if it is not
enabled.
A script file is generated for each database and the file
name is tagged with the date and time, so that you do not have to worry about
overwriting the script file from a daily run.
