Learn About Databases - SQL Server

1. How do I find the SQL Server download?

Go to http://msdn.microsoft.com/vstudio/express/sql and download the SQL Server Express package. SQL Server Express requires the Dot Net v2 Framework and Windows 2000 or later. If you want some reference material, go to microsoft.com and search for SqlServer2k5-BOL_Dec2005.msi for an installable package containing the documentation.

During the installation, leave the SQL Server instance name as 'sqlexpress'. Also there are two kinds of security available. Windows authentication uses the inbuilt Windows NT / 2000 / XP security, whereas SQL Server authentication uses its own security. It is simpler to use the SQL Server authentication, as this makes it clear in SQL scripts and ADO code what user you are connecting as.

2. How do I use SQLCMD to connect to SQL Server?

After downloading and installing SQL Server Express, you can use the SQLCMD command line utility to carry out database tasks.

  sqlcmd -L

The command above lists all databases available on your network.

  sqlcmd -S ComputerName\sqlexpress

The command above starts up SQLCMD, connecting to the SQL instance on ComputerName. If using SQLCMD on a database on your local machine, the computer name is the name of your local machine (you can type 'ipconfig /all' at the command prompt to find the name of your local machine).

  sqlcmd -S .\sqlexpress

The command above starts up SQLCMD, connecting to the SQL instance on your local computer. The dot stands for the local computer name.

  sqlcmd -S .\sqlexpress -U Username -P Password

The command above starts up SQLCMD, connecting to the SQL instance on your local computer using SQL Server authentication. The Username and Password must match the one defined when you installed SQL Server Express.

3. How do I list all databases known to SQL Server?

This assumes you are using the instance of SQL server installed locally on your computer. This instance should have the name 'sqlexpress'.

  sqlcmd -S .\sqlexpress [ -U Username -P Password ]

The command above starts SQLCMD and connects to the local instance. The details in square brackets are for SQL Server authentication.

  select name from sys.databases
  go

The command above makes SQLCMD list all databases, including the internal databases master, tempdb, model and msdb.

4. How do I list all tables in one database?

SQL Server supports multiple databases, and each database stores multiple tables. First start SQLCMD as described, then you can use the following commands:

  use Database
  select name from sysobjects
  go

This lists all tables in Database including the internal tables.

  use Database
  select name from sysobjects where name = Tablename
  go

The command above checks whether Tablename exists in the database.

5. How do I use variables with SQLCMD?

  :setvar ID value

The command above sets the SQLCMD scripting variable ID equal to value. If the value includes spaces, it should be enclosed in double quotes.

  sqlcmd -V ID=value ...

The same scripting variable can be set on the command line when SQLCMD is invoked. This can be useful if calling SQLCMD automatically.

  : ... $(ID) ...

The command above shows how to use the value of the variable ID inside SQLCMD. The identifier can be used as table names, column names, values in queries etc. SQLCMD can also use environment variables in the same way. These can be set at the command prompt in the usual way, and they are then available to SQLCMD as it runs.

  :setvar ID

This clears the value of ID, which is the same as deleting it.

  set TAB=Database.Tablename
  sqlcmd ...
  select * from $(TAB)
  go

The command above shows how to set the TAB environment variable on the command line, how to start SQLCMD (other options not shown), and use the TAB variable in a select statement.

  sqlcmd -S .\sqlexpress -v ID=value -i file.sql

The command above starts SQLCMD, sets the variable ID to the specified value, runs the file file.sql then quits. The file should contain valid SQL syntax statements and SQLCMD commands, and the statements can use the identifier $(ID) in expressions and as column and table names.

6. What other useful SQLCMD options are there?

When SQLCMD is running you can use SQL commands by just typing them in. Also you can issue commands directly to SQLCMD by preceding them with a colon ':'. Some useful commands are shown next.

  create database Name
  go

The command above creates a new database.

  create table Tablename
  (
    column1 INT NOT NULL,
    column2 CHAR(20) NOT NULL
  )

The command above creates a table in the current database with the name and structure given. Any SQL command can be used at the SQLCMD prompt.

  :help

The command above prints a help page.

  :quit

The command above quits SQLCMD and returns to the command prompt.

  :r file.sql

The command above reads input from file.sql and the commands will be executed if 'go' is included in the file.

  delete Tablename
  go

The command above deletes all rows in Tablename. It does not delete the actual table.

  use Database
  select count(*) from Tablename
  go

The command above shows the number of records (rows) in Database.Tablename.

  :listvar

The command above shows the values of all scripting variables, not including environment variables.

  :out file.txt

The command above redirects the output of SQLCMD to the specified file.

  :out STDOUT

The command above restores the output of SQLCMD to the screen.

  select net_transport from sys.dm_exec_connections
  where session_id = @@SPID
  go

The command above lists the type of connection used by SQL Server. This will be one of shared memory, pipes, TCP/IP since SQL Server has different transport types available to it.