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 parameter?