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 ”’[email protected]+”’,
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=’
[email protected]+’;Initial Catalog=’+ @DBName + ‘;Integrated Security=SSPI;” ‘ +
@FolderName + REPLACE(@SERVERNAME,’,’,’_’)+’_’[email protected]+’_’+
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.

Previous articleDatabase Size
Next articlePHP Pagination

Latest Articles