How do I get SSMS to use the relative path of the current script with :r in sqlcmd mode like SSDT does?

If I have foo.sql and bar.sql in the same folder, foo.sql can reference bar.sql when run from SSDT in sqlcmd mode with :r ".\bar.sql". However, SSMS won't find it. Procmon shows SSMS is looking in %systemroot%\syswow64:

How do I tell SSMS to look in the folder that the current script is saved to without explicitly declaring the path?

Getting a relative path in SSMS is not that straight-forward since you are not executing the script; SSMS has loaded the script into memory and is executing its text. So the current directory/folder is the default process starting folder. You can see this by running the following in SQLCMD mode in SSMS:

!! PWD

However, I did find a kinda-sorta way to do this. I admit that this is not the super-most ideal way to do this, however, it currently seems to be the only way to get a true relative path (given that setting the path in a variable isn't really "relative" per se).

So what you can do is:

• Execute a DOS command to find foo.sql and store the path to that file in a text file in a folder that you can get from SSMS, either because it is a hard-coded path or because it uses an environment variable that is available to both the DOS command and to SQLCMD mode in SSMS

• When storing the path in that file, store it as a SQLCMD mode command that sets a variable to that path

• Import that text file into SSMS using :r and it will set that variable to the desired path

!! CD C:\ & FOR /F %B IN ('DIR /B /A -HS /S foo.sql') DO ECHO :setvar mypath "%~dpB" > %TEMP%\relative_path.txt

:r $(TEMP)\relative_path.txt

:r $(mypath)\bar.sql


Notes:

-
The above method assumes only 1 file on the system is named foo.sql. If more than one file has that name, the last one found will be the path set in the relative_path.txt file

-
Doing the CD C:\ will start at the root of the C: drive. This is probably not the most efficient place to start. If you generally have your SQL files in an area such as C:\Users{YourLogin}\Documents\Visual Studio 2013\Projects, then just change the CD command to get closer to the destination, such as:

!! CD C:\Users\{YourLogin}\Documents\Visual Studio 2013 & FOR ...


StackExchange Database Administrators Q#91312, answer score: 8