Generate SQL Server Scripts by Name Pattern

Repository: https://github.com/himanshukhyap/SQLServer

The SSMS Generate Scripts wizard is fine the first time. By the fifteenth time — clicking through the same six screens to script the same twenty tables before a release — it stops being fine.

What you usually want is much simpler to say than to click:

Give me the structure of every table starting with TRVL_. For CityClass and CityMaster, give me the data too. And don't drop anything that already has rows in it.

These three stored procedures do exactly that. You install them once per database, call them with a name pattern, and copy the generated script out of the results grid.

All three target SQL Server 2012 and later — no DROP ... IF EXISTS, no STRING_AGG, no TRIM. They were built and tested against SQL Server 2012 SP3 Express.

The shared idea: name patterns

Every procedure takes a comma-separated list of LIKE patterns. The pattern is used exactly as you type it, so you decide where the wildcards go.

Two things worth knowing:

• If you leave the schema off, the pattern matches in every schema. Write dbo.%TRVL% to pin it down.

• _ is a LIKE wildcard too, so TRVL_% also matches TRVLX.... For a literal underscore use the escape form TRVL[_]%.

1. Tables

The table procedure takes two lists, because in practice you want two different things from your tables.

EXEC dbo.usp_GenerateTableScript
     @TableName     = 'TRVL_%',                 -- structure only
     @TableNameData = 'CityClass, CityMaster';  -- structure + INSERT statements

Transaction tables get their schema scripted. Lookup and master tables get their rows as well. If a table matches both lists, the data list wins and the table still appears only once.

SAFE mode: never drop what has data in it

This is the part that matters on a live database. For the @TableName (structure-only) list, the default @StructureMode = 'SAFE' never emits a DROP TABLE. It creates the table only if it is missing, and otherwise adds the columns that are missing:

IF OBJECT_ID(N'dbo.TRVL_Booking', N'U') IS NULL
CREATE TABLE [dbo].[TRVL_Booking]
(
     [Id] INT IDENTITY(1,1) NOT NULL
    ,[Ref] VARCHAR(20) NOT NULL
    ,[Remarks] NVARCHAR(200) NULL
    ,CONSTRAINT [PK_TRVL_Booking] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO

/* add any columns that are missing */
IF COL_LENGTH(N'dbo.TRVL_Booking', N'Remarks') IS NULL
    ALTER TABLE [dbo].[TRVL_Booking] ADD [Remarks] NVARCHAR(200) NULL;
GO

Indexes are guarded the same way, so you can run the script twice without errors:

IF NOT EXISTS (SELECT 1 FROM sys.indexes
               WHERE object_id = OBJECT_ID(N'dbo.TRVL_Booking')
                 AND name = N'IX_B_Ref')
CREATE NONCLUSTERED INDEX [IX_B_Ref] ON [dbo].[TRVL_Booking] ([Ref] ASC);

There is one case SQL Server simply will not allow: adding a NOT NULL column with no DEFAULT to a table that already has rows. Rather than letting you hit error 4901, the script checks first and tells you what to do:

IF COL_LENGTH(N'dbo.TRVL_Booking', N'MustFill') IS NULL
BEGIN
    IF NOT EXISTS (SELECT 1 FROM [dbo].[TRVL_Booking])
        ALTER TABLE [dbo].[TRVL_Booking] ADD [MustFill] INT NOT NULL;
    ELSE
        PRINT N'SKIPPED [dbo].[TRVL_Booking].[MustFill] -- NOT NULL with no DEFAULT and the table has rows; add it by hand.';
END
GO

SAFE mode only adds things. It will not change the type, nullability or default of a column that already exists, it will not drop a column that is no longer in the source, and it will not add a missing PRIMARY KEY / UNIQUE / CHECK constraint to a table that already exists. All three of those can lose data, so they stay a deliberate, hand-written migration.

If you actually want the destructive version, ask for it: @StructureMode = 'DROP'.

Data scripting

Tables in @TableNameData are scripted as DROP + CREATE + INSERT, because the point there is a fresh copy.

SET IDENTITY_INSERT [dbo].[CityMaster] ON;
GO
INSERT INTO [dbo].[CityMaster] ([CityId], [CityName], [ClassId], [IsActive])
     VALUES (3, N'Ratnagiri''s', 3, 0);
GO
SET IDENTITY_INSERT [dbo].[CityMaster] OFF;
GO

IDENTITY_INSERT is only emitted for tables that actually have an identity column. Computed columns and rowversion are excluded automatically. Values are round-trip safe for the awkward types:

• datetimeoffset keeps its original offset (+05:30), rather than being converted to UTC

• varbinary / image come out as 0x... literals

• hierarchyid, geometry and geography round-trip through their binary form

• embedded quotes, newlines and XML entities survive intact

• NULL stays NULL

You can narrow what gets scripted:

EXEC dbo.usp_GenerateTableScript
     @TableNameData = 'CityMaster',
     @TopRows       = 500,
     @WhereClause   = 'IsActive = 1',
     @BatchSize     = 250;   -- a GO after every 250 INSERTs

Parameters

2. Stored procedures

EXEC dbo.usp_GenerateProcScript @SPName = '%TRVL%, %EXP%';

For each match you get the SET ANSI_NULLS / QUOTED_IDENTIFIER options the module was created under, a guarded DROP PROCEDURE, and then the definition exactly as stored — original formatting, comments and casing preserved, byte for byte.

Two options are worth knowing about.

@ScriptAs = 'ALTER' rewrites the leading CREATE PROCEDURE into ALTER PROCEDURE. This matters because DROP + CREATE throws away every permission granted on the procedure, while ALTER keeps them. The rewrite only touches the real statement — a CREATE mentioned in a header comment, or the word RECREATE, is left alone.

@IncludeGrants = 1 emits the GRANT / DENY statements alongside, which is the other way to survive a DROP + CREATE:

GRANT EXECUTE ON [dbo].[usp_TRVL_GetBooking] TO [AppRole];

Encrypted procedures (WITH ENCRYPTION) cannot be scripted — SQL Server does not expose their definition. They are listed in the output with a comment instead of a body, and no DROP is emitted for them, because dropping an object the script cannot recreate would destroy it permanently.

@ExcludeSPName takes the same kind of list, which is handy for skipping the backups everybody's database accumulates:

EXEC dbo.usp_GenerateProcScript
     @SPName        = '%',
     @ExcludeSPName = '%_bak, %_old, %_temp';

3. Views and functions

EXEC dbo.usp_GenerateViewFuncScript @Name = '%TRVL%, %EXP%';

Views and functions need something the other two do not: dependency order. A view can be built on another view; a function can call another function. If you drop and create them in alphabetical order, a SCHEMABINDING chain falls apart.

So the output is split into two sections — all the DROPs in reverse dependency order, then all the CREATEs in dependency order:

/* =============== DROP (reverse dependency order) =============== */
    DROP VIEW [rpt].[vw_TRVL_Summary];
    DROP VIEW [dbo].[vw_TRVL_Base];
    DROP FUNCTION [dbo].[fn_TRVL_Total];
    DROP FUNCTION [dbo].[fn_TRVL_Tax];
GO

/* =============== CREATE (dependency order) =============== */
    ... fn_TRVL_Tax, then fn_TRVL_Total, then vw_TRVL_Base, then vw_TRVL_Summary

The depth comes from sys.sql_expression_dependencies, computed with an iteration cap so a circular reference between functions cannot spin forever.

Pick what you want with @ObjectTypes:

@ObjectTypes = 'VIEW'        -- views only
@ObjectTypes = 'FUNCTION'    -- all three function kinds
@ObjectTypes = 'V,IF'        -- views + inline table-valued functions

@ScriptAs = 'ALTER' works here too, with one honest caveat the procedure will tell you about. SQL Server refuses to ALTER a schema-bound object while another schema-bound object references it, so the script puts a warning at the top naming exactly which objects will fail:

/* !!! WARNING ----------------------------------------------------
   SQL Server cannot ALTER a schema-bound object while another
   schema-bound object references it. The statements below will
   fail with error 3729 for:

      [dbo].[fn_TRVL_Tax]  is bound by  [dbo].[fn_TRVL_Total]

Use CREATE mode for those — DROP + CREATE handles the whole chain in the right order.

Indexed views are handled: the clustered index is emitted before the nonclustered ones, which is the order SQL Server requires, and because ALTER VIEW silently drops a view's indexes, the CREATE INDEX statements follow the definition so they get rebuilt.

Descriptions carry over

If your columns have descriptions — the Description box in the SSMS table designer, stored as the MS_Description extended property — all three procedures script them:

IF NOT EXISTS (SELECT 1 FROM sys.extended_properties
               WHERE major_id = OBJECT_ID(N'dbo.TRVL_Booking')
                 AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.TRVL_Booking'), N'Ref', 'ColumnId')
                 AND name = N'MS_Description')
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Agent''s booking reference',
         @level0type=N'SCHEMA', @level0name=N'dbo',
         @level1type=N'TABLE',  @level1name=N'TRVL_Booking',
         @level2type=N'COLUMN', @level2name=N'Ref';
ELSE
    EXEC sys.sp_updateextendedproperty ...

It is written as add-or-update rather than a plain sp_addextendedproperty, so the script stays re-runnable and picks up edited text on a second pass. Tables and views get object-level and column-level descriptions; procedures get object-level. Parameter descriptions are not scripted.

Getting the script out of SSMS

Each procedure returns the script as a single nvarchar(max) column and also PRINTs it in 4000-character chunks split on line boundaries.

For anything large, switch to text results:

1. Ctrl+T — Query > Results To > Results to Text

2. Tools > Options > Query Results > SQL Server > Results to Text → set Maximum characters displayed in each column to 8192

Or copy the PRINT output from the Messages tab, which has no such limit.

Common recipes

-- Ship a schema change to a live database: create what's missing, add new columns
EXEC dbo.usp_GenerateTableScript @TableName = 'TRVL_%';

-- Rebuild the lookup tables somewhere else, data and all
EXEC dbo.usp_GenerateTableScript @TableNameData = 'Mst_%';

-- Everything for one module, in one script
EXEC dbo.usp_GenerateTableScript    @TableName = '%TRVL%';
EXEC dbo.usp_GenerateViewFuncScript @Name      = '%TRVL%';
EXEC dbo.usp_GenerateProcScript     @SPName    = '%TRVL%';

-- Patch procedures without losing their permissions
EXEC dbo.usp_GenerateProcScript @SPName = '%TRVL%', @ScriptAs = 'ALTER';

-- Check what a pattern will match before you script it
SELECT s.name + '.' + t.name FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE t.name LIKE 'TRVL_%' ORDER BY 1;

Where these stop

Worth knowing before you rely on them:

• Foreign keys are not scripted. Dropping and creating individual tables would fail on the dependencies. Script them separately.

• Triggers are not scripted.

• SAFE mode only adds. No type changes, no dropped columns, no retro-fitted constraints on existing tables.

• Encrypted objects cannot be scripted — and are deliberately never dropped either.

• CLR objects are skipped — types PC, X, AF, FS, FT have no readable definition in sys.sql_modules.

• sql_variant data is inserted as nvarchar; the base type information is lost.

• float values are scripted at 16 significant digits, the most SQL Server 2012 offers.

None of this replaces a real migration tool if you have one. But if you are on SQL Server 2012, working across a dozen databases, and you just want a repeatable script you can diff and commit, three stored procedures and a LIKE pattern go a surprisingly long way.