Generating Database Objects Scripts in SQL Server 2005

October 6, 2009

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.