patternsqlMinor
Parameterizing the Schema Used by .sql Scripts in Microsoft SQL Server Management Studio
Viewed 0 times
parameterizingthescriptssqlusedmicrosoftmanagementstudioserverschema
Problem
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
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
- In our .sql scripts, how can I parameterize the
Solution
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:
Example:
:setvar MYSCHEMA foo
SELECT TOP 100 * FROM $(MYSCHEMA).MyTableNameCode Snippets
:setvar MYSCHEMA foo
SELECT TOP 100 * FROM $(MYSCHEMA).MyTableNameContext
StackExchange Database Administrators Q#310339, answer score: 5
Revisions (0)
No revisions yet.