SQL Server has among the finest administration instruments named SQL Server Administration Studio (a.ok.a. SSMS). It provides a variety of options that make the lifetime of builders and DBAs a lot simpler. However typically, there are some points that can’t be fastened utilizing SQL Server Administration Studio. Particularly in terms of working ad-hoc SQL scripts or recovering crashed SQL Server cases. In such use instances, you need to use SQLCMD.
This can be a sponsored article by Devart.Β Devart is at present one of many main suppliers of database administration software program and ALM options for the most well-liked database servers.
Getting began with SQLCMD
On this article, we are going to find out about SQLCMD. It’s a command-line instrument that can be utilized for the next duties:
- Run ad-hoc SQL queries and saved procedures on native and distant servers
- Export SQL question output to textual content or CSV recordsdata
- Handle and administer SQL Server cases and databases on Home windows and Linux
To make question writing in SSMS quicker and simpler, in addition to to reinforce it with further options for database administration and administration, we enhanced it with dbForge SQL Tools, a pack of add-ins seamlessly built-in into SSMS.
Now allow us to start with set up.Β
To put in the SQLCMD utility, you want to choose the native SQL Server shopper instruments whereas putting in SQL Server. You can even set up it individually utilizing the SQL Server set up supervisor.
The SQLCMD utility might be invoked by simply typing SQLCMD in PowerShell or within the command immediate. You’ll be able to see the checklist of choices that can be utilized with SQLCMD by working the next command:
PS C:Usersnisar> SQLCMD -?
That is what the command-line output seems like.
Connecting to SQL Server utilizing SQLCMD
Now, allow us to perceive how to connect with a SQL Server occasion utilizing SQLCMD.
Instance 1: Connect with the default SQL Server occasion
To hook up with SQL Server on a neighborhood machine, use the next SQLCMD command:
C:Usersnisar>sqlcmd -S Nisarg-PC
As you may see, the command output is 1> which reveals that you’re linked to SQL Server.
Word that in case you are connecting to the default occasion of SQL Server on a neighborhood machine, you do not need to explicitly specify the hostname/server title.
Instance 2: Connect with a named SQL Server occasion
Now, allow us to test one other instance exhibiting how to connect with a named SQL Server occasion.
To hook up with a named SQL Server occasion, you want to specify the parameter -S (server title) For example, in case your serverβs title is MyServer and the named occasion is SQL2017, the command to connect with it utilizing SQLCMD can be:
C:>sqlcmd -S Nisarg-PCSQL2019
Right here is the output.
Instance 3: Connect with SQL Server utilizing Home windows authentication and SQL Server authentication
Now, allow us to see how to connect with SQL Server utilizing Home windows and SQL Server authentication.
To hook up with SQL Server utilizing SQLCMD, you need to use Home windows authentication and SQL Server authentication. If you wish to use SQL Server authentication, you want to specify the -U (consumer title) and -p (password) choices. If you don’t specify the password, the SQLCMD utility will ask you to enter the password. The next screenshot illustrates this.
Working with SQLCMD within the interactive mode
On this part, we are going to see methods to run SQLCMD within the interactive mode, execute SQL queries, and think about the output. The interactive mode permits writing SQL statements and instructions. Letβs begin with studying how to connect with SQL Server, enter the interactive mode, and run queries in SQLCMD.
Instance 1: Populate an inventory of databases with house owners
First, connect with your database server utilizing the next command:
C:>sqlcmd -S Nisarg-PC -U sa -p
As soon as the interactive session begins, run the next SQL question within the SQLCMD utility:
use grasp;
choose a.title,b.title from sys.databases a inside be part of sys.server_principals b
on a.owner_sid=b.sid the place a.title not in ('ReportServer','ReportServerTempDB')
and a.database_id>5;
Right here is the question output.
As you may see, the above question has populated the checklist of databases with database house owners.
Instance 2: Examine the present database
First, connect with the database server and execute the next question:
Choose DB_NAME()
Go
That is the question output.
The question has returned the grasp database as a result of I’ve not set the default database for the login which I’m utilizing to connect with SQL Server.
Instance 3: Execute SQL queries
You’ll be able to run SQL queries utilizing SQLCMD by specifying the -Q parameter. For instance, you need to view the checklist of tables created within the SchoolManagement database utilizing SQLCMD. The command ought to be written as follows:
C:>sqlcmd -S Nisarg-PC -d SchoolManagement -Q "choose title from sys.tables"
Check out the question output.
Equally, you may run different queries as nicely. Please word that the login you might be utilizing to connect with SQL Server should have the required permission on the database.
Working with SQLCMD within the command immediate
That is the place we are going to see methods to execute SQL scripts through the command immediate. This characteristic is helpful once you need to run automation duties, bulk operations, and long-running queries that don’t require any consumer enter.
I’ve created an SQL script that accommodates an SQL question that’s used to populate the checklist of objects created within the WideWorldImporters database. The question is as follows:
use [WideWorldImporters]
go
choose title, type_desc, create_date from sys.objects the place type_desc 'SYSTEM_TABLE'
Add the above question to a SQL script named sp_get_db_objects.sql. Now letβs export the output to a textual content file named database_objects.txt.
For that, weβll use the next choices:
- -o: Specify the vacation spot file. On this demo, the vacation spot textual content file is named WideWorldImportores_objects.txt.
- -i: Specify the situation of the SQL script. On this demo, the SQL script is named DBObjects.sql.
Now, letβs run the next command:
sqlcmd -S Nisarg-PC -i D:ScriptsDBObjects.sql -o D:ScriptsWideWorldImportores_objects.txt
As soon as the command is efficiently accomplished, itβs time to overview the textual content file.
As you may see within the above screenshot, the question has been executed efficiently.
Now, allow us to take one other instance. Right here we are going to learn to generate a backup of StackOverflow2010 utilizing a SQL script. The question to generate a backup is as follows:
use grasp
go
backup database [Stackoverflow2010] to disk ='D:SQLBackupsStackoverflow2010.bak' with compression, stats=5
I’ve saved the backup command in an SQL script named StackOverflow2010_backup_script.sql. To execute the script, the SQLCMD command might be as follows:
Screenshot 1:
As you may see within the above screenshot, the backup has been generated.
Screenshot 2:
Utilizing SQLCMD in SQL Server Administration Studio
To make use of SQLCMD in SSMS, first, you need to allow the SQLCMD mode. To do this, choose Question from the menu and choose SQLCMD Mode, as proven within the following picture:
If you wish to set the SQLCMD mode by default, go to Instruments β Choices. In Choices, choose Question execution β SQL Server β Normal and choose the By default, open new queries in SQLCMD mode checkbox.
Now, allow us to see methods to use it.
For instance, I need to get the overall information of the Posts desk of the Stackoverflow2010 database. To do this, the question ought to be written as follows:
:SETVAR TABLENAME "Posts"
:SETVAR DATABASENAME "Stackoverflow2010"
use $(DATABASENAME);
choose depend(1) from $(TABLENAME);
GO
Now, letβs run the question. The next screenshot reveals the question output.
Now, allow us to see methods to use SQLCMD in PowerShell.
Utilizing SQLCMD in PowerShell
You’ll be able to invoke SQLCMD utilizing PowerShell. To do this, you need to set up PowerShell for SQL Server. You’ll be able to learn this article to be taught extra about PowerShell for SQL Server and methods to set up it.
Allow us to take a easy instance. Suppose I need to get the checklist of saved procedures of the WideWorldImporters database. The PowerShell command is as follows:
PS C:WINDOWSsystem32> invoke-sqlcmd -database wideworldimporters -query "choose title from sys.procedures"
Right here is the output.
One other instance reveals methods to export the output of an SQL script to a textual content file utilizing sqlps. Suppose we need to export an inventory of SQL Server agent jobs. I’ve created a script named SQLJobs.sql which retrieves the checklist of SQL jobs. The script accommodates the next T-SQL command:
use [msdb]
go
choose title, description,date_created from Sysjobs
To run the script, I execute the next command in PowerShell for SQL Server.
invoke-sqlcmd -inputfile "D:ScriptsSQLJobs.sql" | Out-File -FilePath "D:ScriptsSQLJobs_List.txt"
As soon as the command is accomplished, I open the output file, which seems as follows.
Superior SQLCMD methods
Listed here are a couple of superior methods that may aid you use SQLCMD extra successfully. I’m going to elucidate them with easy examples.
Instance 1: Present error messages based on the error severity stage
This instance reveals methods to show an error message based on its severity stage. This technique can be utilized by including the -m choice. Suppose you might be working a SELECT question in opposition to a non-existing database object. The command will return βInvalid objectβ, and the severity stage of that error is 16. See the next screenshot.
Letβs check out an error that has a severity stage of 15 β a syntax error
As you may see within the above screenshot, the error severity is 15, due to this fact SQLCMD didn’t present any error.
Instance 2: Exit the SQLCMD session when an error happens
This instance reveals methods to exit your SQLCMD session when a command or question encounters an error. To do this, you need to specify the -b choice. Suppose you need to exit SQLCMD when the question encounters a βdatabase doesn’t existβ error.
Instance 3: Settle for consumer enter
This instance reveals methods to settle for consumer enter whereas executing a T-SQL script. This entails scripting variables in SQLCMD. To display that, I’ve created a script that populates the formal title of the nation. The script makes use of the WideWorldImporters database and the software.International locations desk. The content material of the script is as follows:
use [WideWorldImporters]
Go
choose CountryName, FormalName from software.international locations the place CountryName=$(CountryName)
Go
Now I save the script and execute it utilizing the next SQLCMD command:
sqlcmd -S Nisarg-PC -v CountryName="India" -i D:ScriptsAsia_Countries.sql
Right here is the output.
As you may see, the question returned the formal title India.
Conclusion
On this article, you have got discovered in regards to the SQLCMD command and methods to use it with numerous examples. SQLCMD is a robust instrument that may aid you run scripts, export your output to varied recordsdata, and administer SQL Server. You can even use the DAC (Devoted Admin Connection), which helps entry broken or corrupted database servers.
Lastly, you may all the time energy up the inventory capabilities of SSMS with clever code completion and formatting, supply management, unit testing, command-line automation, and loads of different helpful stuff out there in bundles like dbForge SQL Tools.Β
New customers may give them a free check drive for a whopping 30 days. As soon as you put in the bundle, all of the add-ins might be conveniently out there from each the SSMS menu and Object Explorer. They save me a lot time that I canβt assist however suggest them.