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.
- In our .sql scripts, how can I parameterize the schema that is
prefixed to all table references?
- 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
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
If your scripts are simple enough, one option may be to use the
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';
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
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://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-use-with-scripting-variables?view=sql-server-ver15
:setvar MYSCHEMA foo SELECT TOP 100 * FROM $(MYSCHEMA).MyTableName
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.