4

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?
2
  • 1
    There is also the weird "feature" from the pre-2005 days where you can have in the same batch something like "CREATE SCHEMA a CREATE TABLE t" and the t table is now in the a schema. Very weird and not really practical, since it has to be in the same batch (which I why I don't propose it as an answer). Also a user in a database can have a default schema, in case that is helpful. Commented Mar 30, 2022 at 14:13
  • Have you considered that you may be overcomplicating things? Using a parameterised schema is cool, but it's going to be an extra maintenence burden in the long term. Why not use a simpler solution like having a database each, instead of having a schema each? Commented Mar 31, 2022 at 8:24

3 Answers 3

2

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

Object Explorer showing tables foo.a and bar.a in database se310339

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

5

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
3
  • 1
    Way too slow in typing. Ha! Nicely done. Upvoted. Commented Mar 30, 2022 at 13:23
  • I have found where I can enable SQLCMD mode in SSMS (menu item Query --> SQLCMD Mode). The only remaining question is... Must I set MYSCHEMA to foo within the .sql script itself? Or, is there a way I can set it outside of the script in the SSMS environment? Doing so would prevent me from having to modify my scripts when I want to switch between schemas.
    – Dave
    Commented Mar 30, 2022 at 13:25
  • 1
    You can wrap the call for SQLCMD inside a batch file and it can either supply the argument or prompt the user for one. But otherwise, nothing that I know of. SSMS wasn't designed for that kind of interactivity. You could always leave the variable null and test for that before continuing. Depends on your desired workflow. stackoverflow.com/questions/6652890/… Commented Mar 30, 2022 at 13:32
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.