In SQL Server Management Studio what is SQLCMD mode?
In SQL Server Management Studio I would like to know what is SQLCMD mode?
Answer
I did some more research, so here's my understanding of this to extend what has been written so far:
What is SQLCMD
SQLCMD.exe is a console utility included in the instalation of SQL Server 2005 and higher. You can typically find it in a path like c:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE.
It is a simple scripting environment that allows automation of tasks related to SQL server. For example, you can write and execute a script that will login to a specific instance of SQL Server, execute scripts from a given directory on this connection and store the output in a specified file.
Invoke-Sqlcmd cmdlet was introduced with SQL Server 2008 as a mean to replace this tool with a standardized, Powershell-based approach, preserving most of the original syntax and functionality.
What is SQLCMD mode in SSMS
In SSMS, SQLCMD mode is a script execution mode that simulates the sqlcmd.exe environment and therefore accepts some commands that are not part of T-SQL language.
Unlike sqlcmd.exe, it contacts the database using SqlClient (the same way as SSMS), not ODBC data provider, so in some aspects it might have different behaviour than sqlcmd.exe.
Executing scripts in SQLCMD mode allows to use commands typical to sqlcmd.exe environment. However, there's no IntelliSense or debugging support for SQLCMD mode, so maintaining scripts that mix clean T-SQL with SQLCMD-specific code can be a pain. Therefore, it should be used only when it's necessary.
Example use case
Let's suppose that a company has a naming convention for databases that include environment in the name, eg: MyDb_Prod