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.