Following an earlier article about scripting your database objects, I made a stored procedure that scripts all databases for you automatically and writes the script to a folder. This script was made in SQL Server 2005. This is useful, because you now have all objects such as tables, stored procedures, views and functions in one script.

SQL
CREATE PROCEDURE [dbo].[sp_GenDBscript] @vcDatabase as Varchar(50)
AS
/*
Getest 27-7-2011 met de volgende settings
C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2>sqlpubwiz
e script -schemaonly -d DWH_NL_REF -S MSINTFR07666\BENL_DWH_1 c:\script.sql


Example:
EXEC dbo.[sp_GenDBscript] 'DWH_NL_REF'
*/


DECLARE @vcPath as Varchar(150)
DECLARE @vcCMD as Varchar(150)
DECLARE @vcParam as Varchar(150)
DECLARE @vcTotcmd as Varchar(250)
DECLARE @vcDate as Varchar(10)
DECLARE @vcServer as Varchar(150)
DECLARE @vcTextPath as Varchar(150)
DECLARE @vcTextFile as Varchar(150)
DECLARE @vcMkdirStr as Varchar(150)
DECLARE @vcTime as Varchar(150)
DECLARE @iRC as Int


Set @vcTime = REPLACE(Convert(varchar,getdate(), 114),':','')
SET @vcDate = Convert(varchar, getdate(), 112)
SET @vcTextPath = 'F:\DWH_NL\SCRIPTS\' + @vcDatabase
SET @vcTextFile = @vcDatabase + '_' + @vcDate + '_' + @vcTime + '.sql'
SET @vcMkdirStr = 'mkdir ' + @vcTextPath
SET @vcServer = @@SERVERNAME


--checking if dir exist, If it doesnt exist it creates it
EXEC @iRC = master.dbo.xp_cmdshell @vcMkdirStr, NO_OUTPUT
IF @iRC <> 0
BEGIN
 exec master.dbo.xp_cmdshell @vcMkdirStr , NO_OUTPUT
END


SET @vcPath = '"C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2\SqlPubWiz.exe"'
SET @vcParam = ' script ' + @vcTextPath + '\' + @vcTextFile + ' -S ' + @vcServer + ' -schemaonly -d ' + @vcDatabase
SET @vcTotcmd = @vcPath + @vcParam


print @vcTotcmd


EXEC master..xp_cmdshell @vcTotcmd

Frequently asked questions

How do I script all objects of a SQL Server database automatically?

Use the stored procedure sp_GenDBscript. It calls SqlPubWiz.exe through xp_cmdshell and writes a script with tables, stored procedures, views and functions to a folder.

Which program does sp_GenDBscript use to script a database?

SqlPubWiz.exe, the Publishing Wizard from the ToolsPublishing folder of SQL Server. The procedure starts the program with xp_cmdshell and the -schemaonly option.

How do I call sp_GenDBscript?

Run EXEC dbo.[sp_GenDBscript] with the database name as the parameter, for example 'DWH_NL_REF'. The procedure creates a file named after the database, the date and the time.

Which objects end up in the sp_GenDBscript script?

All objects such as tables, stored procedures, views and functions end up together in one script. The script holds only the schema, because the procedure uses the -schemaonly option.

Why does sp_GenDBscript use xp_cmdshell?

The procedure has to start SqlPubWiz.exe on the command line. It also creates the folder with mkdir if it does not exist yet. Both go through xp_cmdshell.

Comments

Your email address is not shown. A photo comes from Gravatar, if you have one.

  1. How do I make a date dimension?

    SQL Server24 Feb 2009 · 12.4k reads
    12.4k

  2. Calculating running totals

    SQL Server24 May 2011 · 5.3k reads
    5.3k

  3. Date calculations

    SQL Server5 Dec 2008 · 20.3k reads
    20.3k
(3 rows affected)SQL Server