Parameterizing the Schema Used by .sql Scripts in Microsoft SQL Server Management Studio

Question

I am running a local instance of Microsoft SQL Server 15 (2019) and am using Microsoft SQL Server Management Studio v18.9.1 (SSMS) as the client to work through the process of creating the tables needed for my application. Please note that the solution to my inquiry below needs to be backwards-compatible with Microsoft SQL Server 13 (2016).

This is all running under Windows 10.

A colleague and I are working on implementing a database. I am defining the tables, writing .sql scripts to create these tables, and writing .sql scripts to insert sample data. My colleague will write code to parse through the real data we have stored in an ad hoc manner in flat files and programmatically insert the data into the database I am developing. He will use the script I wrote to insert sample data as a guide in using the API that allows him to programmatically inset our real data into our database.

During my iterative development, I am constantly running scripts to create our tables, insert sample data into them, and make some queries. I'll then iterate by making any needed changes to my scripts, dropping all tables, and then repeating.

My colleague and I will clearly be stepping on each other. For example, I may drop all tables while he's trying to programmatically insert some data. Or, he may programmatically insert some data in between the time I run my script to insert sample data and run my script to make some queries, yielding unexpected results.

For this reason, I'm using two schemas. (Here, I use what I think of as the "namespace" sense of the word "schema".) Call them dbo (i.e., the default schema) and foo. I will use foo and my colleague will use dbo.

In my .sql scripts, I have all table references prefixed with "dbo.". Rather than hardcoding the schema in the scripts and having to frequently search / replace ".dbo" with ".foo" and vice versa, I would like to parameterize the schema the scripts should use.

Questions

1. In our .sql scripts, how can I parameterize the schema that is prefixed to all table references?
2. Then, from within SSMS, how do I set that parameter so that when I execute a script, it runs against the schema I've specified in the parameter?

Answer 1

If your scripts are simple enough, one option may be to use the default_schema functionality.

Consider the following demo.

create database se310339;
go
use se310339;
go
create schema foo;
go
create schema bar;
go
create user foo 
    without login 
    with default_schema = foo;
alter role db_owner add member foo;
create user bar 
    without login 
    with default_schema = bar;
alter role db_owner add member bar;
go
exec as user = 'foo';
create table a (i int);
revert
exec as user = 'bar';
create table a (i int);
revert
go
select 
    schema_name([schema_id]) as sch,
    [name] as tbl
from sys.tables
where [name] = N'a';

sch	tbl
foo	a
bar	a

Note both tables are visible in Object Explorer

You can either log in as the user with the appropriate default_schema you wish to use or (if you are sufficiently permissioned yourself), you can use the execute as syntax as in the demo.

Note that you can use the "double dot" syntax to reference the default schema in context. See Double Dot table qualifier on SO proper

Answer 2

If you are willing to use SQLCMD mode, you can parameterize just about anything. More details and examples of what you can do is here: https://learn.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-use-with-scripting-variables?view=sql-server-ver15

Example:

:setvar MYSCHEMA foo 

SELECT TOP 100 * FROM $(MYSCHEMA).MyTableName

Answer 3

You can take advantage of sqlcmd Utility scripts within SSMS. It's there in the link to the documentation. Select "SQLCMD Mode" from the Query menu. Then you just have to use the :SETVAR command (again, in the docs) to create a variable like this:

:SETVAR Myschema dbo

SELECT * FROM $(Myschema).Test1 AS pm

You can set it at the script level like that, or, you can programmatically control it from the command line.