Generating Database Objects Scripts in SQL Server 2005

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

/****** Object:  Table [dbo].[ServerNames]

	Script Date: 09/22/2009 16:18:56 ******/

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.