SQL Azure: How to execute a common DDL script across all schemas?

Question

Is there a built-in way to execute a common DDL script across all schemas?

I'm working on a multi-tenant application which creates a database schema for each tenant. Each schema contains the same table definitions for each tenant. For example:

Schema named "tenant1" contains tables: tenant1.Users, tenant1.HistoryRecords, etc.
Schema named "tenant2" contains tables: tenant2.Users, tenant2.HistoryRecords, etc.

When I add a field I want it to be added in tenant1 schema, tenant2 schema, etc.

Initial thoughts: I have a table which contains the schema names and related information for the tenant. I'm thinking of adding a database version field to this table to keep track of schema changes. I would then create a stored procedure which accepts the DDL script and schema version as parameters.

CREATE PROCEDURE UpdateSchema(DDLScript, InitialSchemaName, DbVersion)
    @DDLScript nvarchar(5000), 
    @InitialSchemaName nvarchar(10), 
    @DbVersion nvarchar(5)...

The script would loop through the set of schemas, running the DDL Script for each one, replacing the InitialSchemaName with the schema name for the current loop, and commit the changes for all if successful.

Is this a reasonable plan forward, or am I missing a more common approach?

Clay · Accepted Answer · 2015-09-27T20:35:36.680

You have to be super careful with a multi-tenant environment. There are so many easy ways to hork things up. As you've indicated, your DDL script has to be edited per schema and this means changing object names inside the script. This is scary, but I understand it's necessary. It introduces a vector for SQL injection.

I hope you have one or more "safe" schemas to test with. Heck, I hope you have a whole test world to test with. But - all worries aside, here's a scaffold of a script I've used in the past to apply changes to a multi-schema environment:

create type dbo.ObjectNamesType as table ( Name sysname )
go
create procedure RunDDL( @scriptTemplate nvarchar( max ), @objName sysname, @objType nvarchar( 10 ), @schemas dbo.ObjectNamesType readonly )
as
begin
    set nocount on

    declare @script nvarchar( max )
    declare @objectName nvarchar( 256 )

    declare c cursor for
        select N'[' + s.name + N'].[' + o.name + N']' 
        from 
            sys.objects o 
            inner join
            @schemas s
            on
                o.schema_id = schema_id( s.Name )
        where
            o.name = @objName
            and
            o.type = @objType
    open c
        while ( 1=1 )
        begin
            fetch next from c into @objectName
            if ( @@fetch_status != 0 ) break;
            select @script = replace( @scriptTemplate, N'@objectName', @objectName )
            exec sys.sp_executesql @script
        end
    close c
    deallocate c
end
go

...and to test it...

declare @script nvarchar( max )
declare @objName sysname
declare @objType sysname
declare @schemas dbo.ObjectNamesType

insert @schemas values( 'dbo' )
select @objName = 'someTable'
select @objType = 'u'
select @script = 'select * from @objectName' --> not really ddl, eh?
exec dbo.RunDDL @script, @objName, @objType, @schemas

The one I actually used is a lot more...complicated - so I just left the juicy parts behind. A couple of notes:

The inputs are set up in such a way that the script can be run against a group of schemas. This lets you run it on your test schema first and see if it's okay - and assuming you like it, you can then run it against the remaining schemas en masse.

In my world, the @templateScript, @objName and @objType reside in a table which I join to - and are not passed in. I wouldn't recommend running such a procedure with inputs from the outside world, as this is an invitation to catastrophe...but for illustration/testing, it serves the purpose. Also, in my world, the input table has a version ID and a sequence. For any schema at version x, we run all the scripts in sequence and assuming success, up that schema's version to y. Each script applies to a single object.

The point here is that you'll want to make a routine workflow out of updates to the database - and this procedure is at the core of that workflow.

Note that it selects from sys.objects rather than just believing the inputs. This is just another small safeguard to keep your scripts from barfing over typos in names. If the number of objects we edit fails to match the number of objects specified, we log a warning for ourselves.

A procedure of this type should also try/catch the actual execution of the script and should log everything it tries along the way. It should roll things back on error. Make sure you have plenty of transaction log space as even tiny DDL has a way of causing immense amounts of change.

It works it's way through the specified objects, editing the @templateScript into a @script variable and then running the @script variable with sys.sp_executesql. This way, it's never changing the source variable so the replace target remains intact.

It is unfortunate that you can't use variables for tsql object names, or you could reduce the surface for injection attack. Thus, the recommendation for an inputs table rather than arguments. It also means SQL can't really do anything to parameterize/reuse the statement/execution plan - but then again, this is not something that gets run billions of times, right?

Thanks for the detailed feedback. I plan to test updates on a restored copy of the database in a staging environment prior to running on the live production database. — Trevor, Sep 28 '15 at 18:34

SQL Azure: How to execute a common DDL script across all schemas?

1 Answers1