Scripting out object level SQL

A while ago I stumbled across Scriptio, a ClickOnce application to do just this. It has a very simple interface, and you can tell that it was designed to solve a problem, rather than as an end solution.

Tab One - Database Objects

To get a list of database objects, enter a Server name, select either Use Windows Authentication or enter a Username and password and press Connect. This should populate the Database drop down. Select a database and the bottom section should be populated with the objects that are scriptable within your database. From here, it is a simple case of selecting the appropriate options from the top right hand section (Include CREATE, Include DROP etc) and selecting the appropriate objects from the bottom section (there is a handy Script All option which selects everything. I have never been able to get the Schema or Type drop downs to populate, which may be something to do with the design of my objects.

Tab Two - More Options

The options tab allows choices of how many files to create (or even if you want to create a file), where to store them and some additional scripting objects (Include collation, Qualify CREATEs with Schema etc).

Tab Three - Generated Script

This tab displays the SQL generated - in an single box, allowing the content to be copied to clipboard and used elsewhere. Just as an observation, I've never seen the "Save As..." button enabled but I've never needed to use it either so that hasn't been a problem.

Sample script - a table

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Book]') AND type in (N'U'))
DROP TABLE [dbo].[Book]
GO

/****** Object: Table [dbo].[Book] Script Date: 06/16/2008 17:30:18 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Book](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[Author] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[PublisherId] [int] NULL,
[ISBN] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[PublishedYear] [int] NULL,
[BookAgeId] AS (case datepart(year,getdate())-[PublishedYear] when (0) then (1) when (1) then (2) else (3) end)
) ON [PRIMARY]

GO

Sample script - a stored procedure

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetBooks]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[up_GetBooks]
GO

/****** Object: StoredProcedure [dbo].[up_GetBooks] Script Date: 06/16/2008 17:30:19 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jane Dallaway
-- Create date: 12 June 2008
-- Description: Get all books, in full detail
-- =============================================
CREATE PROCEDURE up_GetBooks
AS
BEGIN
SET NOCOUNT ON;

SELECT Book.Id, Book.Title, Book.Author, Publisher.Name, Book.ISBN, Book.PublishedYear, BookAge.Name BookAge
FROM Book
INNER JOIN BookAge
ON Book.BookAgeId = BookAge.Id
INNER JOIN Publisher
ON Book.PublisherId = Publisher.Id

END

GO

Summary

This is a great utility to allow scripting on an object by object basis. The source code is available too.