SQL Server: Database schema script generation

So you’ve tried to “Script Database as” (via right click) and only get the empty database creation script:

This is fine, but you want dependent objects (e.g. tables, stored procedures, etc) so you try the “Generate script for dependent objects” setting (Tools -> Options):

But you get the following error saying “… of dependent objects is not supported”:

Annoyingly there is a different option / wizard you need to use; Generate scripts.
Right click -> Tasks -> Generate Scripts…

This will open up the wizard, choose the Database (yes, the right click context doesn’t carry through) and tick the option for all objects:

The next screen you’ll want to choose your options, I like including “If NOT EXISTS” and “Drop” since I can then highlight and recreate a table from the script with ease:

The DDL created for a Stored Procedure will look something a bit like:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Hello]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Hello]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Hello]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Hello] 
	@param AS INT
AS
BEGIN
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT @param
END
' 
END
GO

Note: This does NOT work for encrypted stored procedures, they are encrypted to specifically prevent you from extracting them.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s