Saturday, June 20, 2015

Querying a SQL Server database with SQLCMD

Commandline access to the program is perhaps one of the nicest features any program can have. They can do most things you want done as long as you understand the switches and when & how to use them. A good wizard can take you to most of the things these days, but cannot beat the commandline. Did I hear you say, short and sweet?

SQLCMD is a commandline tool for working with SQL Server. Well regarding the various arguments(switches) it takes are obtained from this help file.


Now let me start SQLCMD with the switch -S. S stands for Server. In the present case, the SQL Server Instance 'RegencyPark' is installed in my Windows 8.1 laptop named 'Hodentek8'. I will then ask the program to change the database context to 'Northwind', the default is 'master' using the statement 'Use Northwind and Go'. Now follow the script here as I query the Customers table:

C:\Users\Jayaram>sqlcmd -S Hodentek8\RegencyPark
1> Use Northwind
2> go
Changed database context to 'Northwind'.
1> Select * from Customers
2> Where CompanyName like 'A%'
3> GO
CustomerID CompanyName                              ContactName                    ContactTitle
              Address                                                      City            Region
       PostalCode Country         Phone                    Fax
---------- ---------------------------------------- ------------------------------ -----------------
------------- ------------------------------------------------------------ --------------- ---------
------ ---------- --------------- ------------------------ ------------------------
ALFKI      Alfreds Futterkiste                      Maria Anders                   Sales Representat
ive           Obere Str. 57                                                Berlin          NULL

       12209      Germany         030-0074321              030-0076545
ANATR      Ana Trujillo Emparedados y helados       Ana Trujillo                   Owner
              Avda. de la Constitución 2222                                México D.F.     NULL
       05021      Mexico          (5) 555-4729             (5) 555-3745
ANTON      Antonio Moreno Taquería                  Antonio Moreno                 Owner
              Mataderos  2312                                              México D.F.     NULL

       05023      Mexico          (5) 555-3932             NULL
AROUT      Around the Horn                          Thomas Hardy                   Sales Representat
ive           120 Hanover Sq.                                              London          NULL
       WA1 1DP    UK              (171) 555-7788           (171) 555-6750
(4 rows affected)
If you type EXIT for the last line above, you will return to: