There are two ways of executing operating system commands from SQL Server Management Studio. The first method is with the use of the [dbo].[xp_cmdshell] system extended stored procedure. The xp_cmdshell system extended stored procedure spawns a Windows command shell and passes in a string for execution. Any output is returned as rows of text.
xp_cmdshell { 'command_string' } [, no_output ]
‘command_string’ is the string that contains a command to be passed to the operating system. The data type of ‘command_string’ is VARCHAR(8000) or NVARCHAR(4000), with no default value. The no_output parameter is optional which specifies that no output should be returned to the client.
The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account. xp_cmdshell operates synchronously, that is, control is not returned to the caller until the command-shell command is completed.
Here’s an example of getting the list of SQL scripts from a certain directory using the “dir” operating system command:
EXECUTE [master].[dbo].[xp_cmdshell] 'dir C:\Scripts\*.sql'
Here’s another example which returns the list of SQL Servers on the network:
EXECUTE [master].[dbo].[xp_cmdshell] 'sqlcmd -Lc'
The second method of executing operating system commands from SQL Server Management Studio is with the use of the SQLCMD Mode. To enable SQLCMD mode, go to the Query menu option of SQL Server Management Studio and select SQLCMD Mode or click on the SQLCMD Mode icon in the toolbar (the icon that has the window in the background and an exclamation point in the foreground). The sqlcmd utility allows the entry of Transact-SQL statements, system procedures and script files at the command prompt. But with the SQLCMD Mode, you are not limited to executing the sqlcmd utility. You can also execute any operating system commands by simply prefixing the command with 2 exclamation points (!!).
Using the same examples with the xp_cmdshell extended stored procedure, here’s how to get a list of SQL Server scripts from a certain directory using the “dir” operating system command:
!!dir C:\Scripts\*.sql
Here’s another example which returns the list of SQL Servers on the network:
!!sqlcmd -Lc