sql安装弹出sqlcmd_使用SQL Server命令行（sqlcmd）

最新推荐文章于 2024-07-31 13:38:36 发布

culuo4781

最新推荐文章于 2024-07-31 13:38:36 发布

阅读量1k

点赞数 2

文章标签：数据库 python linux java mysql

原文链接：https://www.sqlshack.com/working-sql-server-command-line-sqlcmd/

版权

sql安装弹出sqlcmd

介绍 (Introduction)

Sqlcmd allows executing queries, T-SQL sentences and SQL Server scripts using the command line.

Sqlcmd允许使用命令行执行查询，T-SQL语句和SQL Server脚本。

In the previous article How to work with the command line and Azure to automate tasks, we worked with the sqlcmd in Azure.

在上一篇文章如何使用命令行和Azure来自动执行任务中，我们在Azure中使用了sqlcmd。

In this new chapter, we will show the following examples in a local SQL Server using sqlcmd:

在这一新的章节中，我们将在使用sqlcmd的本地SQL Server中显示以下示例：

1. connect to SQL Server
  连接到SQL Server
2. check the current database
  检查当前数据库
3. list databases
  列出数据库
4. check if the SQL Server is case sensitive
  检查SQL Server是否区分大小写
5. check the SQL Server edition
  检查SQL Server版本
6. check the SQL Server Authentication
  检查SQL Server身份验证
7. list the variables set
  列出变量集
1. back up a database
  备份数据库
2. run a T-SQL script and receive the output in a file
  运行T-SQL脚本并接收文件中的输出
3. work with variables
  使用变量
4. list the table names of a database
  列出数据库的表名
5. list the column names of a database
  列出数据库的列名
6. check all the commands
  检查所有命令
7. exit if the command fails
  如果命令失败则退出
8. display error messages according to the error level
  根据错误级别显示错误消息
9. accept user input
  接受用户输入
1. run sqlcmd in SSMS
  在SSMS中运行sqlcmd
2. set the sqlcmd mode by default in SSMS
  在SSMS中默认设置sqlcmd模式
1. invoke sqlcmd using PowerShell
  使用PowerShell调用sqlcmd
2. run scripts in SQL PowerShell (check table fragmentation)
  在SQL PowerShell中运行脚本（检查表碎片）
3. output verbose results
  输出详细结果
1. How to work with a Dedicated Administrator Connection (DAC)
  如何使用专用管理员连接（DAC）
When to use sqlcmd mode, interactive mode, DAC, SSMS, PowerShell
何时使用sqlcmd模式，交互模式，DAC，SSMS，PowerShell

要求 (Requirements)

Sqlcmd installed in a Windows Machine (Linux supports sqlcmd, but it is slightly different). SQLCMD安装在Windows机器上（Linux支持SQLCMD，但略有不同）。

入门 (Getting Started)

In interactive mode, you can write the input and interact using the command line.

在交互模式下，您可以编写输入并使用命令行进行交互。
1. To connect to your local machine, specify the SQL Instance name and the credentials:
  
  要连接到本地计算机，请指定SQL实例名称和凭据：
  
  sqlcmd -S DESKTOP-5K4TURF\SQLEXPRESS -E
  
  sqlcmd -S DESKTOP-5K4TURF \ SQLEXPRESS -E
  
  The –S value is to specify the SQL Server name of the instance and -E is to specify a trusted connection. If you do not specify the SQL Server name, it will try to connect to the local machine.
  
  -S值用于指定实例SQL Server名称，-E值用于指定可信连接。如果未指定SQL Server名称，它将尝试连接到本地计算机。
  
  When you connect, you will see the number 1>:
  
  连接时，您将看到数字1> ：
  
  The number 1> means that it is connected and ready to receive sentences to execute.
  
  数字1>表示已连接并准备接收要执行的语句。
  
  If you enabled SQL Server Authentication, you will need to specify a user name and a user password (I am assuming that the user is already created). Note that you will need to EXIT of sqlcmd to login with this credential.
  
  如果启用了SQL Server身份验证，则需要指定用户名和用户密码（假设用户已经创建）。请注意，您需要退出 sqlcmd才能使用此凭据登录。
  
  sqlcmd -S DESKTOP-5K4TURF\SQLEXPRESS -U jsmith
  
  sqlcmd -S DESKTOP-5K4TURF \ SQLEXPRESS -U jsmith
  
  The command line will ask you the password. You can optionally specify the password (not recommended, but sometimes is the only way to work):
  
  命令行将询问您密码。您可以选择指定密码（不建议使用，但有时是唯一的工作方式）：
  
  sqlcmd -S DESKTOP-5K4TURF\SQLEXPRESS -U jsmith -P
  Mypwd$%1234565
  
  sqlcmd -S DESKTOP-5K4TURF \ SQLEXPRESS -U jsmith -P
  Mypwd $％1234565
2. When a SQL Server Login is created, you can define the default database you want to log in. If it is not specified, the master database is the default one.
  
  创建SQL Server登录名后，可以定义要登录的默认数据库。如果未指定，则主数据库为默认数据库。
```
 
select DB_NAME()
GO
 
```
3. The following sentences will list the databases in the SQL Instance:
  
  以下句子将列出SQL实例中的数据库：
```
 
select name from sys.databases
go
 
```
  In the sys.databases table, you have all the database information:
  
  在sys.databases表中，您具有所有数据库信息：
  
  You can also use the sp_databases stored procedure:
  
  您还可以使用sp_databases存储过程：
```
 
Sp_databases
Go
 
```
4. The following T-SQL Sentences are used to detect the collation information including if the machine is case sensitive or not:
  
  以下T-SQL语句用于检测排序规则信息，包括计算机是否区分大小写：
```
 
SELECT SERVERPROPERTY('COLLATION')
GO
 
```
  The information displayed will be as follows:
  
  显示的信息如下：
  
  Modern_spanish is the collation, CI means case insensitive and CS is case sensitive. AS means Accent Sensitive and AI is Accent Insensitive.
  
  Modern_spanish是排序规则，CI表示不区分大小写，CS表示区分大小写。 AS表示对口音敏感，而AI则对口音不敏感。
  
  You can also check the information, with the sp_helpsort procedure:
  
  您还可以使用sp_helpsort过程检查信息：
```
 
sp_helpsort
go
 
```
  The information displayed is the following:
  
  显示的信息如下：
  
  Modern-Spanish, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
  
  现代西班牙语，不区分大小写，区分重音，不区分假名类型，不区分宽度
5. You can check the SQL Server Edition, using the following T-SQL sentences:
  
  您可以使用以下T-SQL语句检查SQL Server Edition：
```
 
SELECT SERVERPROPERTY('EDITION')
GO
 
```
  The result is the following:
  
  结果如下：
6. Before Azure, there were two options to Authenticate to SQL Server:
  
  在Azure之前，有两种方法可以向SQL Server进行身份验证：
  1. Windows Authentication where you can use an Active directory account or a local Windows account.
    Windows身份验证，您可以在其中使用Active Directory帐户或本地Windows帐户。
  2. Windows Authentication and SQL Authentication where you can also authenticate using an account created in SQL Server.
    Windows身份验证和SQL身份验证，您还可以使用在SQL Server中创建的帐户进行身份验证。
  To detect the authentication, you can use the following sentences:
  
  要检测身份验证，可以使用以下语句：
```
 
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly')
GO
 
```
  The result displayed is the following:
  
  显示的结果如下：
  
  If the result is 0, it means that both authentications are enabled. If it is 1, only Windows Authentication is enabled.
  
  如果结果为0，则表示同时启用了两种身份验证。如果为1，则仅启用Windows身份验证。
7. In order to list all the variables set, run the following command in sqlcmd:
  
  为了列出所有设置的变量，请在sqlcmd中运行以下命令：
  
  :ListVar
  
  ：ListVar
  
  It will show all the variables set:
  
  它将显示所有设置的变量：
You can run sqlcmd as commands. You can run scripts in command mode.

您可以将sqlcmd作为命令运行。您可以在命令模式下运行脚本。
1. In the next example, we will show how to run a script using sqlcmd and show the results in another file.
  
  在下一个示例中，我们将展示如何使用sqlcmd运行脚本并将结果显示在另一个文件中。
  
  We will first create a script file named columns.sql with the following sentences:
  
  我们将首先创建一个名为column.sql的脚本文件，其中包含以下语句：
  
  select * from adventureworks2014.information_schema.columns
  
  从Adventureworks2014.information_schema.columns中选择*
  
  In the cmd, run the following command to invoke sqlcmd:
  
  在cmd中，运行以下命令以调用sqlcmd：
  
  sqlcmd -S DESKTOP-5K4TURF\SQLEXPRESS -E -i c:\sql\columns.sql -o c:\sql\exit.txt
  
  sqlcmd -S DESKTOP-5K4TURF \ SQLEXPRESS -E -ic：\ sql \ columns.sql -oc：\ sql \ exit.txt
  
  -i is used to specify the input. You specify the script file with the queries.
  -o is used to show the results of the input in a file.
  
  -i用于指定输入。您可以使用查询指定脚本文件。
  -o用于在文件中显示输入结果。
  
  The exit.txt file will be created:
  
  将创建exit.txt文件：
  
  If we open the file, we will see the output results:
  
  如果打开文件，将看到输出结果：
2. We will first create a script to back up the database named backup.sql:
  
  我们首先将创建一个脚本来备份名为backup.sql的数据库：
```
 
BACKUP DATABASE [AdventureWorks2014] TO  DISK = N'C:\SQL\backup.bak'
GO
 
```
  In the cmd run the following command:
  
  在cmd中运行以下命令：
  
  sqlcmd -S DESKTOP-5K4TURF\SQLEXPRESS -E -i c:\sql\backup.sql -o
  c:\sql\output.txt
  
  sqlcmd -S DESKTOP-5K4TURF \ SQLEXPRESS -E -ic：\ sql \ backup.sql -o
  c：\ sql \ output.txt
  
  The output will be similar to this one:
  
  输出将与此类似：
  
  The commands will create a backup in a file named backup.sql in the c:\sql folder:
  
  这些命令将在c：\ sql文件夹中名为backup.sql的文件中创建备份：
3. You can work with variables in sqlcmd. The following example will set the variable DATABASENAME with the value adventureworks2014 and then we change the context to the database specified:
  
  您可以在sqlcmd中使用变量。以下示例将使用值Adventureworks2014设置变量DATABASENAME，然后将上下文更改为指定的数据库：
```
 
:SETVAR DATABASENAME "adventureworks2014"
USE $(DATABASENAME);
GO
 
```
  The result displayed is the following:
  
  显示的结果如下：
  
  As you can see, SETVAR is used to specify the value of the variable. Then you need to use $() for the variable.
  
  如您所见，SETVAR用于指定变量的值。然后，您需要使用$（）作为变量。
  
  Another example is to set the variable CONTACTTYPEID to 3 and use it in the where clause to find a contact type ID according to the value of the variable:
  
  另一个示例是将变量CONTACTTYPEID设置为3，并在where子句中使用它根据变量的值查找联系人类型ID：
```
 
:SETVAR CONTACTTYPEID 3
SELECT [ContactTypeID]
      ,[Name]
      ,[ModifiedDate]
  FROM [Person].[ContactType]
where contacttypeid=$(CONTACTTYPEID)
GO
 
```
  The result displayed is the following:
  
  显示的结果如下：
4. You can list the tables of the database using the information_schema.tables view. We will first create a script named tables.sql. This script contains the tables and views:
  
  您可以使用information_schema.tables视图列出数据库的表。我们首先将创建一个名为tables.sql的脚本。该脚本包含表和视图：
```
 
--Script name: tables.sql
select table_name from adventureworks2014.information_schema.tables
GO
 
```
  Next, we will invoke sqlcmd to execute the script.
  
  接下来，我们将调用sqlcmd来执行脚本。
  
  sqlcmd -E -i c:\sql\tables.sql -o c:\sql\output.txt -S DESKTOP-
  5K4TURF\SQLEXPRESS
  
  sqlcmd -E -ic：\ sql \ tables.sql -oc：\ sql \ output.txt -S桌面-
  5K4TURF \ SQLEXPRESS
  
  The result displayed are the following in the output.txt file:
  
  在output.txt文件中显示的结果如下：
5. The following sentences will list the table names and the column names of a database in a script named columns.sql:
  以下句子将在名为columns.sql的脚本中列出数据库的表名和列名：
```
 
--Filename columns.sql
select table_name, column_name from adventureworks2014.information_schema.columns
GO
 
```
  In the cmd run this command:
  
  在cmd中运行以下命令：
  
  sqlcmd -E -i c:\sql\columns.sql -o c:\sql\output.txt -S DESKTOP-
  5K4TURF\SQLEXPRESS
  
  sqlcmd -E -ic：\ sql \ columns.sql -oc：\ sql \ output.txt -S DESKTOP-
  5K4TURF \ SQLEXPRESS
  
  The result of the output.txt is the following:
  
  output.txt的结果如下：
6. You can check all the sqlcmd commands using this command:
  
  您可以使用以下命令检查所有sqlcmd命令：
  
  Sqlcmd -?
  
  Sqlcmd-？
  
  This command will list all the commands available:
  
  该命令将列出所有可用的命令：
7. The following command will exit if it fails using the –b parameter:
  
  如果使用–b参数失败，以下命令将退出：
  
  sqlcmd -E -q “create table adventureworks” –b -S DESKTOP-
  5K4TURF\SQLEXPRESS
  
  sqlcmd -E -q“创建表Adventureworks” –b -S DESKTOP-
  5K4TURF \ SQLEXPRESS
  
  The command will exit if there is an error:
  
  如果出现错误，该命令将退出：
8. If there is an error, the error is displayed. However, according to the error level, you can stop this behavior by default using the -m option.
  
  如果有错误，则显示错误。但是，根据错误级别，您可以默认使用-m选项停止此行为。
  
  Here it is an example about this:
  
  这是关于此的示例：
  
  The following command shows an error message:
  
  以下命令显示错误消息：
  
  However, if you add the –m 16, the error will no longer be displayed because the error has the level of 15:
  
  但是，如果添加–m 16，则错误将不再显示，因为错误级别为15：
  
  sqlcmd -E -q “create table adventureworks” -m 16 -S
  DESKTOP-5K4TURF\SQLEXPRESS
  
  sqlcmd -E -q“创建表Adventureworks” -m 16 -S
  DESKTOP-5K4TURF \ SQLEXPRESS
  
  -m 16 will show only the errors higher than 16. As you can see the error message is no longer displayed
  
  -m 16将仅显示高于16的错误。如您所见，错误消息不再显示
9. The following example will run a SQL script with one variable. The example will create a database specified by the user.
  
  以下示例将运行一个带有一个变量SQL脚本。该示例将创建用户指定的数据库。
  
  We will first create a script named createdb.sql with the following content:
  
  我们将首先创建一个名为createdb.sql的脚本，其内容如下：
```
 
--file createdb.sql
CREATE DATABASE $(DATABASENAME);
GO
 
```
  Next, in the cmd we will run the database specifying the database name:
  
  接下来，在cmd中，我们将运行数据库，并指定数据库名称：
  
  sqlcmd -E -v DATABASENAME=”Userinput” -i
  c:\sql\createdb.sql
  
  sqlcmd -E -v DATABASENAME =“用户输入” -i
  c：\ sql \ createdb.sql
  
  The command will create a database named Userinput.
  
  该命令将创建一个名为Userinput的数据库。
  
  In sqlcmd you can run the sp_databases stored procedure:
  
  在sqlcmd中，可以运行sp_databases存储过程：
```
 
Sp_databases
GO
 
```
  And you will be able to see the database created:
  
  您将能够看到创建的数据库：
1. Yes, in SSMS, click on your query and select Query>SQLCMD Mode:
  
  是的，在SSMS中，单击查询，然后选择“查询”>“ SQLCMD模式”：
  
  The following example will create a database named sales in SSMS.
  
  以下示例将在SSMS中创建一个名为sales的数据库。
```
 
:SETVAR DATABASENAME "sales"
create database $(DATABASENAME);
GO
 
```
  If everything is OK, a database named sales will be created:
  
  如果一切正常，将创建一个名为sales的数据库：
2. Yes, to do this, go to Tools>Options in SSMS and check the By default, open new queries in SQLCMD mode.
  
  是的，要执行此操作，请转到SSMS中的“ 工具”>“选项” ，然后选中“ 默认情况下，以SQLCMD模式打开新查询”。
1. PowerShell can be used to invoke sqlcmd. To open PowerShell for SQL Server, go to the Windows Search and write sqlps:
  
  可以使用PowerShell调用sqlcmd。要打开用于SQL Server的PowerShell，请转到Windows搜索并编写sqlps：
  
  In sqlps, write these cmdlets to run the sp_who stored procedure:
  
  在sqlps中，编写以下cmdlet来运行sp_who存储过程：
  
  invoke-sqlcmd -query “sp_who”
  
  invoke-sqlcmd-查询“ sp_who”
  
  Note that if you have SSMS 17 or later, SQL PowerShell is installed separately. For more information about installing SQL PowerShell, refer to our link:
  
  请注意，如果您具有SSMS 17或更高版本，则将单独安装SQL PowerShell。有关安装SQL PowerShell的更多信息，请参考我们的链接：
  - What is new in SSMS 17; PowerShell and DAX
  - SSMS 17中的新增功能； PowerShell和DAX
2. It is possible to run SQL Server scripts with PowerShell. The following example will show the fragmentation of the table of the table Person.Address in the Adventureworks database.
  
  可以使用PowerShell运行SQL Server脚本。下面的示例将显示Adventureworks数据库中表Person.Address的表的碎片。
  
  We will first create a script named fragmentation.sql:
  
  我们将首先创建一个名为fragmentation.sql的脚本：
```
 
DECLARE @db_id SMALLINT=DB_ID('AdventureWorks');  
DECLARE @object_id INT=OBJECT_ID(N'AdventureWorks.Person.Address');
 
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, 
@object_id, NULL, NULL , 'LIMITED');  
GO
 
```
  In PowerShell for SQL Server, run this script:
  
  在用于SQL Server的PowerShell中，运行以下脚本：
  
  Invoke-sqlcmd –inputfile “c: \sql\fragmentation.sql” | Out-File
  -filePath “C:\sql\outps.txt”
  
  调用sqlcmd –输入文件“ c：\ sql \ fragmentation.sql” | 外档
  -filePath“ C：\ sql \ outps.txt”
  
  The output of the outps.txt file will be the following:
  
  outps.txt文件的输出如下：
3. Verbose is used to display information that is not displayed by default. For example, the command print is not displayed by default. Let’s take a look to an example.
  
  详细信息用于显示默认情况下不显示的信息。例如，默认情况下不显示命令打印。让我们来看一个例子。
  
  In sqlps, run this cmdlet:
  
  在sqlps中，运行以下cmdlet：
  
  Invoke-Sqlcmd -Query “PRINT ‘HELLO SQLSHACK'”
  
  Invoke-Sqlcmd-查询“ PRINT'HELLO SQLSHACK'”
  
  The cmdlet will not return any value. However, if you run with the parameter verbose, the output can be displayed:
  
  该cmdlet将不返回任何值。但是，如果使用参数verbose运行，则可以显示输出：
  
  Invoke-Sqlcmd -Query “PRINT ‘HELLO SQLSHACK'”
  –verbose
  
  Invoke-Sqlcmd-查询“ PRINT'HELLO SQLSHACK'”
  –详细
1. If SQL Server fails to connect in SSMS or other tools, it is possible to try a DAC connection. This connection is connection allows to diagnostic and verify the problems of the Database Server. When the SQL Server is corrupt and it is not possible to connect to it, DAC connection usually works.
  
  如果SQL Server无法在SSMS或其他工具中连接，则可以尝试DAC连接。该连接是允许诊断和验证数据库服务器问题的连接。当SQL Server损坏并且无法连接到它时，通常可以使用DAC连接。
  
  The following example shows how to connect to a SQL Server database:
  
  下面的示例显示如何连接到SQL Server数据库：
  
  sqlcmd -S DESKTOP-5K4TURF -E -A -d master
  
  sqlcmd -S DESKTOP-5K4TURF -E -A -d master
  
  -A is used to specify a DAC connection and -d is used to specify the database to connect.
  
  -A用于指定DAC连接，-d用于指定要连接的数据库。
  
  A DAC connection requires the SQL Browser service to be started and enabled. To enable the SQL Browser service, if it is disabled, you can use the following commands:
  
  DAC连接需要启动和启用SQL Browser服务。要启用SQL Browser服务（如果已禁用），可以使用以下命令：
  
  sc config sqlbrowser start=demand
  
  sc config sqlbrowser start =需求
  
  If it is enabled, the message will be the following:
  
  如果启用，则将显示以下消息：
  
  To start the service, you can use the following commands:
  
  要启动该服务，可以使用以下命令：
  
  net start sqlbrowser
  
  净启动sqlbrowser
Use interactive mode when you need to run multiple queries and administrative tasks. The sqlcmd command line mode is used when you have specific tasks like a backup. Use it when you have a specific task in mind. DAC is used for disaster recovery (for example when the master database is damaged and you cannot access to SQL Server using SSMS or other conventional tools). SSMS in sqlcmd mode can be used to create scripts. It is great to debug and program large scripts to be used later in the command line mode.

需要运行多个查询和管理任务时，请使用交互模式。当您执行诸如备份之类的特定任务时，将使用sqlcmd命令行模式。当您有特定的任务时使用它。 DAC用于灾难恢复（例如，当主数据库损坏并且您无法使用SSMS或其他常规工具访问SQL Server时）。可以使用sqlcmd模式下的SSMS创建脚本。对大型脚本进行调试和编程，以备日后在命令行模式下使用时，非常好。

Use PowerShell if you have other PowerShell scripts and you need to integrate some sqlcmd invocations to it.

如果您有其他PowerShell脚本，并且需要将一些sqlcmd调用集成到其中，请使用PowerShell。

结论 (Conclusion)

Sqlcmd is a very powerful feature that can help us to automate tasks in SQL Server. You can run scripts and save the results of your queries in a text file.

Sqlcmd是一项非常强大的功能，可以帮助我们在SQL Server中自动执行任务。您可以运行脚本并将查询结果保存在文本文件中。

Previous article in this series:

本系列的上一篇文章：
- How to work with the command line and Azure to automate tasks 如何使用命令行和Azure自动执行任务