Trabajar con la línea de comando de SQL Server (sqlcmd)

Introducción

Sqlcmd se utiliza para ejecutar consultas, de las sentencias referidas a T-SQL y scripts de SQL Server utilizando la línea de comandos.

En el artículo anterior de Cómo trabajar con la línea de comando y Azure para automatizar tareas, nosotros hemos trabajado con el sqlcmd utilizando Azure.

En este nuevo capítulo, les mostraremos los siguientes ejemplos en un servidor SQL local que utiliza sqlcmd:

Deba trabajar con el modo interactivo sqlcmd, incluido cómo
1. Conectarse a un servidor SQL Server
2. Verificar la base de datos actual
3. Listar bases de datos
4. Debe verificar si SQL Server distingue entre mayúsculas y minúsculas
5. Debe revisar la edición de SQL Server
6. Se debe verificar la autenticación del servidor SQL
7. Enumerar el conjunto de variables
Ejecutando sqlcmd en modo comando incluyendo cómo
1. Copia de seguridad de una base de datos
2. Ejecutar un script T-SQL y recibir la salida en un archivo
3. Trabajar con variables
4. Se debe enumerar los nombres de tabla de una base de datos
5. Enumerar los nombres de columna de una base de datos
6. Verificar todos los comandos
7. Salir si el comando falla
8. Mostrar mensajes de error según el nivel de error
9. Aceptar entrada del usuario
3. Trabajar en SSMS en modo sqlcmd incluyendo cómo
1. Ejecutar sqlcmd en SSMS
2. Configurar el modo sqlcmd por defecto en SSMS
Trabajar con PowerShell incluyendo cómo
1. Invocar sqlcmd usando PowerShell
2. Ejecutar scripts en SQL PowerShell (verificar la fragmentación de la tabla)
3. Resultados detallados de salida
DAC
1. Cómo trabajar con una conexión de administrador dedicada (DAC)
Cuándo usar el modo sqlcmd, modo interactivo, DAC, SSMS, PowerShell

Requisitos

Sqlcmd instalado en una máquina con Windows (Linux admite sqlcmd, pero es ligeramente diferente).

Empezando

Trabajar con el modo interactivo sqlcmd

En modo interactivo, se puede escribir la entrada e interactuar usando la línea de comando
1. Cómo se puede conectar a SQL Server usando sqlcmd
  Para conectarse a la máquina local, especifique el nombre de la instancia de SQL y sus credenciales:
  
  sqlcmd -S DESKTOP-5K4TURF\SQLEXPRESS -E
  
  El valor –S es sirve para especificar el nombre del servidor SQL de la instancia y -E es utilizado para especificar una conexión confiable. En el caso de que no se especificara el nombre de SQL Server, intentará conectarse a la máquina local
  
  Cuando te conectes, verás el número 1>:
  
  El número 1> significa que está conectado y listo para recibir oraciones para ejecutar
  
  Si se habilitó la autenticación de SQL Server, usted deberá especificar un nombre de usuario y una contraseña de usuario (supongo que el usuario ya está creado). Debe tener en cuenta que deberá SALIR de sqlcmd para iniciar sesión con esta credencial
  
  sqlcmd -S DESKTOP-5K4TURF\SQLEXPRESS -U jsmith
  
  La línea de comando requerirá la contraseña. Opcionalmente, se puede especificar la contraseña (no se recomienda esta opción, pero a veces es la única forma de trabajar):
  
  sqlcmd -S DESKTOP-5K4TURF\SQLEXPRESS -U jsmith -P
  Mypwd$%1234565
2. Cómo verificar la base de datos actual en sqlcmd
  
  En el momento en que se crea un inicio de sesión de SQL Server, se puede definir la base de datos predeterminada en la que se desea iniciar sesión. Si no se especifica, la base de datos maestra esta es la predeterminada
  
  1
  2
  3
  4
  
  select DB_NAME()
  GO
3. Cómo enumerar las bases de datos usando sqlcmd
  
  Las siguientes oraciones enumerarán las bases de datos en la instancia de SQL:
  
  1
  2
  3
  4
  
  select name from sys.databases
  go
  
  En la tabla sys.databases, tiene toda la información de la base de datos:
  
  También puede usar el procedimiento almacenado sp_databases:
  
  1
  2
  3
  4
  
  Sp_databases
  Go
4. Cómo verificar si SQL Server puede distinguir entre mayúsculas y minúsculas en sqlcmd
  
  Las siguientes oraciones T-SQL serán utilizadas para detectar la información de clasificación, incluso si la máquina distinguiera entre mayúsculas y minúsculas:
  
  1
  2
  3
  4
  
  SELECT SERVERPROPERTY('COLLATION')
  GO
  
  La información que se mostrará será la siguiente:
  
  Modern_spanish es la clasificación, CI significa que no es sensible a mayúsculas y minúsculas y CS que es sensible a mayúsculas y minúsculas. AS significa acento sensible y AI es acento insensible
  
  También se puede verificar la información, con el procedimiento sp_helpsort:
  
  1
  2
  3
  4
  
  sp_helpsort
  go
  
  La información que se muestra a continuación es la siguiente:
  
  Español moderno, insensible a mayúsculas y minúsculas, sensible al acento, insensible al kanatipo, insensible al ancho
5. Cómo verificar la edición de SQL Server en SQL
  
  Puede consultar la Edición de SQL Server, utilizando las siguientes oraciones T-SQL:
  
  1
  2
  3
  4
  
  SELECT SERVERPROPERTY('EDITION')
  GO
  
  El resultado es el siguiente:
6. Cómo verificar la autenticación de SQL Server en sqlcmd
  
  Previamente al inicio de Azure, habían dos opciones para autenticarse en SQL Server:
  1. Autenticación de Windows donde puede usar una cuenta de Active Directory o una cuenta local de Windows
  2. Autenticación de Windows y Autenticación de SQL donde también puede autenticarse utilizando una cuenta creada en SQL Server
  Para detectar la autenticación, se pueden usar las siguientes líneas de código SQL:
  
  1
  2
  3
  4
  
  SELECT SERVERPROPERTY('IsIntegratedSecurityOnly')
  GO
  
  El resultado mostrado es el siguiente:
  
  Si el resultado que se muestra es 0, significa que ambas autenticaciones están habilitadas. Si es 1, solo está habilitada la autenticación de Windows.
7. Cómo enumerar el conjunto de variables
  
  Para enumerar todas las variables establecidas, usted deberá ejecutar el siguiente comando en sqlcmd:
  
  :ListVar
  
  El mismo que Mostrará todas las variables establecidas:

Ejecutando sqlcmd en modo comando

Puede ejecutar sqlcmd como comandos. Puede ejecutar scripts en modo comando

Cómo ejecutar un script T-SQL y recibir la salida en un archivo en sqlcmd

En el siguiente ejemplo, pasamos a mostrar cómo ejecutar un script usando sqlcmd y registrar los resultados en otro archivo

Primero debemos crear un archivo de script llamado columnas.sql con las el siguiente código:

select * from adventureworks2014.information_schema.columns

En el cmd, ejecute el siguiente comando para invocar sqlcmd:

sqlcmd -S DESKTOP-5K4TURF\SQLEXPRESS -E -i c:\sql\columns.sql -o c:\sql\exit.txt

-i se usa para especificar la entrada. Usted especifica el archivo de script con las consultas
-o se usa para mostrar los resultados de la entrada en un archivo

Se creará el archivo exit.txt:

Si abrimos el archivo, veremos los resultados de salida:
Cómo se puede hacer una copia de seguridad en sqlcmd

Primero deberemos crear un script para hacer una copia de seguridad de la base de datos llamada backup.sql:

1
2
3
4

BACKUP DATABASE [AdventureWorks2014] TO DISK = N'C:\SQL\backup.bak'
GO

En el cmd ejecuta el siguiente comando:

sqlcmd -S DESKTOP-5K4TURF\SQLEXPRESS -E -i c:\sql\backup.sql -o
c:\sql\output.txt

El resultado obtenido será similar a este:

Los comandos deberán crear una copia de seguridad en un archivo llamado backup.sql en la carpeta c:\sql:

Cómo trabajar con variables en sqlcmd

Puede trabajarse con variables en sqlcmd. El siguiente ejemplo definirá la variable DATABASENAME con el valor adventureworks2014 y luego se cambiará el contexto a la base de datos especificada:

:SETVAR DATABASENAME "adventureworks2014"

USE $(DATABASENAME);

El resultado mostrado es el siguiente:

Como se puede observar, SETVAR se utiliza para especificar el valor de la variable. Entonces necesita usar $ () para la variable.

Otro ejemplo consiste en establecer la variable CONTACTTYPEID en 3 y poder usarla en la cláusula where para encontrar un ID de tipo de contacto de acuerdo con el valor de la variable como se muestra a continuación:

:SETVAR CONTACTTYPEID 3

SELECT [ContactTypeID]

,[Name]

,[ModifiedDate]

FROM [Person].[ContactType]

where contacttypeid=$(CONTACTTYPEID)

El resultado sería el siguiente:

Cómo se puede enumerar los nombres de tabla de una base de datos en sqlcmd

Puede enumerar todas las tablas de la base de datos utilizando la vista information_schema.tables. Primeramente crearemos un script llamado tables.sql. Este script contiene las tablas y vistas:

1
2
3
4
5

--Script name: tables.sql
select table_name from adventureworks2014.information_schema.tables
GO

A continuación, invocaremos sqlcmd para ejecutar el script.

sqlcmd -E -i c:\sql\tables.sql -o c:\sql\output.txt -S DESKTOP-
5K4TURF\SQLEXPRESS

Los resultados del comando son los siguientes y se muestran en el archivo output.txt:
Cómo se puede enumerar los nombres de columna de una base de datos en sqlcmd

Las siguientes oraciones podrán enumerar los nombres de las tablas así como los nombres de las columnas de una base de datos en un script llamado columnas.sql:

1
2
3
4
5

--Filename columns.sql
select table_name, column_name from adventureworks2014.information_schema.columns
GO

En el cmd ejecuta este comando:

sqlcmd -E -i c:\sql\columns.sql -o c:\sql\output.txt -S DESKTOP-
5K4TURF\SQLEXPRESS

El resultado del archivo output.txt es el siguiente:
Cómo se puede verificar todos los comandos

Se pueden verificar todos los comandos sqlcmd usando este comando:

Sqlcmd -?

Este comando enumerará todos los comandos existentes:
Cómo se puede salir si el comando falla

El siguiente comando se cerrará si falla usando el parámetro –b:

sqlcmd -E -q “create table adventureworks” –b -S DESKTOP-
5K4TURF\SQLEXPRESS

El comando se cerrará si existe algún error:
Cómo se puede mostrar mensajes de error según el nivel de error

Si hay un error, entonces se mostrará el error. No obstante, de acuerdo con el nivel de error, se puede detener este comportamiento de forma predeterminada utilizando la opción -m.

A continuación existe un ejemplo sobre esto:

El siguiente comando muestra un mensaje de error:

Sin embargo, si agrega –m 16, el error ya no se mostrará debido a que el error tiene el nivel de 15:

sqlcmd -E -q “create table adventureworks” -m 16 -S
DESKTOP-5K4TURF\SQLEXPRESS

-m 16 mostrará solamente los errores superiores a 16. Como puede ver, el mensaje de error ya no se muestra
Cómo se puede aceptar la entrada del usuario

El siguiente ejemplo ejecutará un script SQL con una variable. El ejemplo permitirá crear una base de datos especificada por el usuario

Primero crearemos un script llamado createdb.sql con el siguiente contenido:

1
2
3
4
5

--file createdb.sql
CREATE DATABASE $(DATABASENAME);
GO

A continuación, en el cmd deberemos ejecutar la base de datos especificando el nombre de la base de datos:

sqlcmd -E -v DATABASENAME=”Userinput” -i
c:\sql\createdb.sql

El comando creará una base de datos llamada Userinput.

En sqlcmd puede ejecutar el procedimiento almacenado sp_databases:

1
2
3
4

Sp_databases
GO

Y podrá ver la base de datos creada:

Trabajando en SSMS en modo sqlcmd
1. Cómo ejecutar sqlcmd en SSMS
  
  En SSMS, haga clic en su consulta y seleccione Consulta>Modo SQLCMD:
  
  El siguiente ejemplo creará una base de datos denominada ventas en SSMS.
  
  1
  2
  3
  4
  5
  
  :SETVAR DATABASENAME "sales"
  create database $(DATABASENAME);
  GO
  
  Si todo está bien, seguidamente se creará una base de datos llamada ventas:
2. ¿Cómo podemos establecer el modo sqlcmd por defecto en SSMS?
  
  Para hacer esto, vaya a Herramientas>Opciones en SSMS y marque Por defecto, abra nuevas consultas en modo SQLCMD

Trabajando con PowerShell

Cómo invocar sqlcmd usando PowerShell

PowerShell se puede usar para invocar sqlcmd. Para poder abrir PowerShell para SQL Server, vaya a Windows Search y escriba sqlps:

En sqlps, escriba estos cmdlets para ejecutar el procedimiento almacenado sp_who:

invoke-sqlcmd -query “sp_who”

Es muy importante tomar en cuenta que, si tiene SSMS 17 o posterior, SQL PowerShell se instala por separado. Para obtener más información sobre la instalación de SQL PowerShell, consulte nuestro enlace:
- Qué hay de nuevo en SSMS 17; PowerShell y DAX

Cómo se puede ejecutar scripts en SQL PowerShell (verificar la fragmentación de una tabla)

Es posible ejecutar scripts de SQL Server con PowerShell. El siguiente ejemplo mostrará la fragmentación de la tabla de la tabla Person.Address en la base de datos Adventureworks

Primero se debe crear un script llamado 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');

En PowerShell para SQL Server, ejecute el siguiente script:

Invoke-sqlcmd –inputfile “c: \sql\fragmentation.sql” | Out-File
-filePath “C:\sql\outps.txt”

La salida del archivo outps.txt será la siguiente:

Cómo se puede usar la salida detallada

Verbose se utiliza para mostrar información que no se muestra de manera predeterminada. Por ejemplo, el comando print no se muestra por defecto. Veamos un ejemplo

En sqlps, ejecute este cmdlet:

Invoke-Sqlcmd -Query “PRINT ‘HELLO SQLSHACK'”

El cmdlet no devolverá ningún valor. Sin embargo, si ejecuta con el parámetro detallado, entonces la salida se puede mostrar:

Invoke-Sqlcmd -Query “PRINT ‘HELLO SQLSHACK'”
–verbose

DAC
1. Cómo trabajar con una conexión de administrador dedicada (DAC) en sqlcmd
  
  Si SQL Server no se conecta en SSMS u otras herramientas, es posible probar una conexión DAC. Esta conexión no es otra cosa que la conexión que permite diagnosticar y verificar los problemas del servidor de bases de datos. En el caso de que cuando el SQL Server está dañado y no es posible conectarse a él, la conexión DAC generalmente funciona
  
  El siguiente ejemplo muestra cómo conectarse a una base de datos de SQL Server:
  
  sqlcmd -S DESKTOP-5K4TURF -E -A -d master
  
  -A se usa para especificar una conexión DAC y -d se usa para especificar la base de datos a conectar
  
  Una conexión DAC requiere que se inicie y habilite el servicio del navegador SQL. Para habilitar el servicio del navegador SQL, si el mismo está deshabilitado, entonces puede usar los siguientes comandos:
  
  sc config sqlbrowser start=demand
  
  Si está habilitado, el mensaje será el siguiente:
  
  Para iniciar el servicio, puede usar los siguientes comandos:
  
  net start sqlbrowser
When to use sqlcmd mode, interactive mode, DAC, SSMS, PowerShell

Es importante indicar que cuando use el modo interactivo entonces es necesario ejecutar múltiples consultas y tareas administrativas. El modo de línea de comando sqlcmd se utiliza cuando tiene tareas específicas como una copia de seguridad. Úselo cuando tenga una tarea específica en mente. DAC se utiliza para la recuperación ante desastres (por ejemplo, cuando la base de datos maestra está dañada y no puede acceder a SQL Server usando SSMS u otras herramientas convencionales). SSMS en modo sqlcmd se puede usar para crear scripts. Es genial depurar y programar scripts grandes para usarlos más tarde en el modo de línea de comandos

Use PowerShell si tiene otros scripts de PowerShell y necesita integrar algunas invocaciones sqlcmd

Conclusión

Sqlcmd es una herramienta muy poderosa que puede ayudarnos a automatizar tareas en SQL Server. Permite ejecutar scripts y guardar los resultados de sus consultas en un archivo de texto.

Artículo anterior de esta serie:
- Cómo trabajar con la línea de comando y Azure para automatizar tareas
- Acerca de
- Últimas entradas
Daniel Calbimonte
Daniel Calbimonte es un Microsoft Most Valuable Professional, Microsoft Certified Trainer y Microsoft Certified IT Professional en SQL Server. He is an accomplished SSIS author, Intructor en academias de IT y tienedécadas de experience trabajando con diferentes bases de datos.

Realizó consultorias para empresas gubernamentales, empresas petroleras, sitios web, revistas y universidades alrededor del mundo. Daniel participa regularmente en conferencias de SQL Server y blogs. Escribe material sobre SQL Server y exámenes de certificación.

También realiza la traducción de artículos de SQLShack al español

Ver todas las publicaciones de Daniel Calbimonte
Últimas entradas de Daniel Calbimonte (ver todo)

Cómo construir un cubo desde cero usando SQL Server Analysis Services (SSAS) - December 16, 2019

Fecha de conversión de SQL - December 11, 2019

Funciones frente a los procedimientos almacenados en SQL Server - October 14, 2019
Related posts:

Introducción

Requisitos

Empezando

Conclusión

Related posts: