As a DBA, I have a plethora of queries that I run on a consistent basis, commands that give me information about who’s connected to the instance, how mirroring is performing, what types of permissions a user has, etc. Early in my career I began cataloging these queries. My first file was called ‘useful_sql.txt’ (I still have it to this day) and it grew longer with each learned action. I’d open up SSMS, click the folder icon to open the file and cut/paste any commands I needed to a new editor window. What a pain. As I grew in the profession, I began creating individual .sql files with a meaningful title for each specific query, but I still had to click the folder, find the file, wait for it to open in SSMS then click to execute the query. A little less painful, but still not ideal.
Powershell offered some options but meant I had to leave the query editor.
Here was my solution: SQLCMD mode.
Enabling SQLCMD mode in the query editor allows you to run commands just as you would from the SQLCMD prompt. The command I find most useful is:
:r <filename>
The file is read and the statements executed. Now, instead of the hassle of opening a file and executing the commands thru a multi-click process, a query can be executed without leaving the query editor and keyboard, e.g.
:r I:/scripts/showSQL.sql
This executes the following query stored in the ‘showSQL.sql’ file that shows me who’s running which statement:
SELECT
CONVERT(VARCHAR(8000),(SELECT SUBSTRING(sqltext.text,req.statement_start_offset/2,
CASE
WHEN req.statement_end_offset = -1 THEN 8000
ELSE (req.statement_end_offset-req.statement_start_offset)/2
END))) as 'SQL statement',
req.blocking_session_id,
req.session_id,
req.status,
req.command,
DB_NAME(req.database_id) as "db_name",
sess.login_name as "user name",
req.start_time,
req.cpu_time,
req.total_elapsed_time
FROM
sys.dm_exec_requests req
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS sqltext
JOIN
sys.dm_exec_sessions sess
on req.session_id = sess.session_id
where
req.session_id > 50
order by
req.status |
SELECT
CONVERT(VARCHAR(8000),(SELECT SUBSTRING(sqltext.text,req.statement_start_offset/2,
CASE
WHEN req.statement_end_offset = -1 THEN 8000
ELSE (req.statement_end_offset-req.statement_start_offset)/2
END))) as 'SQL statement',
req.blocking_session_id,
req.session_id,
req.status,
req.command,
DB_NAME(req.database_id) as "db_name",
sess.login_name as "user name",
req.start_time,
req.cpu_time,
req.total_elapsed_time
FROM
sys.dm_exec_requests req
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS sqltext
JOIN
sys.dm_exec_sessions sess
on req.session_id = sess.session_id
where
req.session_id > 50
order by
req.status
For the above example, I have a file share that I’ve mapped to the I: drive. I place all my scripts on that drive in the /scripts directory. Files will be read relative to the directory that SQLCMD is invoked from; from the query editor this usually means c:\windows\systems32. While you could place all your .sql scripts there, it’s not a good idea. I suggest a fully qualified path to a place of your choosing.
SQLCMD mode has to be enabled in the SSMS query editor. It can be done per query editor session or set to open the editor in SQLCMD mode by default:
- To enable SQLCMD mode for the current query editor session, go to the Query menu and select SQLCMD mode.
- To enable SQLCMD mode by default, go to Tools > Options. In the left hand pane single click Query Execution then click the textbox By default, open new queries in SQLCMD mode
There a few other uses for SQLCMD mode as well as some limitations, here’s the link to BOL if you’d like more information:
http://msdn.microsoft.com/en-us/library/ms174187(v=SQL.105).aspx
Happy scripting!
<Disclaimer: the sample script was not created by me but I do not remember from where I copied it. If it’s your work, please post a comment and I’ll be happy to give you credit.>
