sql-stored-procedure-001

Dynamic View for All E_ Tables in SQL Server

Problem

You want a SQL Server view that combines all tables whose names start with the prefix E_, even though those tables are being created and deleted over time.

Limitation

In SQL Server:

• A view cannot be truly dynamic.
• A view must be compiled against specific objects and columns.
• If tables are added or removed, the view will not automatically update.

So the correct approach is to dynamically rebuild the view definition whenever the table list changes.

---

Recommended Solution (Option A)

Stored Procedure that Rebuilds the View

Concept:

1. Query system tables to find all E_% tables.
2. Build a UNION ALL query across them.
3. Recreate the view using dynamic SQL.
4. Run the procedure on a schedule or after table changes.

---

Requirements

You must choose a column strategy:

Option 1 – All E_ tables have identical schema

• Easiest scenario.

Option 2 – Use a template table

• A table (for example dbo.E_Template) defines the canonical column list.
• Only tables matching those columns are included.

Option 3 – Use only shared columns

• More complex.
• Only columns common across all tables are included.

The script below uses Option 2 (Template Table).

---

Stored Procedure: Rebuild the View

CREATE OR ALTER PROCEDURE dbo.Rebuild_v_E_All
    @ViewSchema sysname = N'dbo',
    @ViewName   sysname = N'v_E_All',
    @Prefix     nvarchar(10) = N'E_',
    @TemplateTable sysname = N'E_Template'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @templateObjectId int =
        OBJECT_ID(QUOTENAME(@ViewSchema) + N'.' + QUOTENAME(@TemplateTable));

    IF @templateObjectId IS NULL
    BEGIN
        THROW 50001, 'Template table not found. Create dbo.E_Template (or pass @TemplateTable).', 1;
    END

    ;WITH TemplateCols AS (
        SELECT c.name, c.column_id
        FROM sys.columns c
        WHERE c.object_id = @templateObjectId
    ),
    TargetTables AS (
        SELECT t.object_id, s.name AS schema_name, t.name AS table_name
        FROM sys.tables t
        JOIN sys.schemas s ON s.schema_id = t.schema_id
        WHERE t.name LIKE @Prefix + N'%' ESCAPE N'\'
    ),
    ValidTables AS (
        -- keep only tables that contain ALL template columns
        SELECT tt.object_id, tt.schema_name, tt.table_name
        FROM TargetTables tt
        WHERE NOT EXISTS (
            SELECT 1
            FROM TemplateCols tc
            WHERE NOT EXISTS (
                SELECT 1
                FROM sys.columns c
                WHERE c.object_id = tt.object_id AND c.name = tc.name
            )
        )
    )
    SELECT 1;

    DECLARE @colList nvarchar(max) =
        STUFF((
            SELECT N',' + QUOTENAME(tc.name)
            FROM sys.columns c
            JOIN TemplateCols tc ON tc.name = c.name
            WHERE c.object_id = @templateObjectId
            ORDER BY tc.column_id
            FOR XML PATH(''), TYPE
        ).value('.', 'nvarchar(max)'), 1, 1, N'');

    DECLARE @sql nvarchar(max) = N'CREATE OR ALTER VIEW '
        + QUOTENAME(@ViewSchema) + N'.' + QUOTENAME(@ViewName) + N' AS' + CHAR(10);

    DECLARE @union nvarchar(max) =
        STUFF((
            SELECT CHAR(10) + N'UNION ALL' + CHAR(10)
                 + N'SELECT '
                 + QUOTENAME(vt.schema_name) + N'.' + QUOTENAME(vt.table_name) + N' AS source_table, '
                 + @colList
                 + N' FROM ' + QUOTENAME(vt.schema_name) + N'.' + QUOTENAME(vt.table_name)
            FROM (
                SELECT t.object_id, s.name AS schema_name, t.name AS table_name
                FROM sys.tables t
                JOIN sys.schemas s ON s.schema_id = t.schema_id
                WHERE t.name LIKE @Prefix + N'%'
            ) vt
            ORDER BY vt.schema_name, vt.table_name
            FOR XML PATH(''), TYPE
        ).value('.', 'nvarchar(max)'), 1, LEN(CHAR(10) + N'UNION ALL' + CHAR(10)), N'');

    IF @union IS NULL OR LTRIM(RTRIM(@union)) = N''
    BEGIN
        -- create empty view with correct structure
        SET @sql += N'SELECT CAST(NULL AS sysname) AS source_table, ' + @colList + N' WHERE 1=0;';
    END
    ELSE
    BEGIN
        SET @sql += @union + N';';
    END

    EXEC sys.sp_executesql @sql;
END
GO