Connect to SQL Server from command prompt – list tables and database

Posted on in Categories , last updated December 18, 2006

Q. How do I connect to Microsoft SQL Server from command prompt? I just wanted to see list of tables and database.

A. MS- SQL Server is relational database management system.

Its primary query language is Transact-SQL, an implementation of the ANSI/ISO standard Structured Query Language (SQL) used by both Microsoft and Sybase.

There is command line tool available and it is called as sqlcmd. you need to enter the word GO after every command.

On the Start menu click Run. In the Open box type cmd, and then click OK to open a Command Prompt window.

At the command prompt, type sqlcmd.

Press ENTER.

Let us say your username is vivek and password is foo, use:
C:> sqlcmd -U vivek -P foo

Once connected you should see 1> prompt. Type following command to use database called sales:
use sales
GO

To list tables type:
sp_help
OR
select * from SYSOBJECTS where TYPE = 'U' order by NAME

To List all the databases on the server:
sp_databases

To list fields in a table called foo:
sp_help tablename
sp_help foo

Posted by: Vivek Gite

The author is the creator of nixCraft and a seasoned sysadmin and a trainer for the Linux operating system/Unix shell scripting. He has worked with global clients and in various industries, including IT, education, defense and space research, and the nonprofit sector. Follow him on Twitter, Facebook, Google+.

38 comment

  1. Hi ,
    How can i connect to informix database using unix testbox.
    please let me know the command and the environmental changes that i have to make.

    Thanks and Regards
    Balaji Vinu

  2. Hi

    How to get the column names from a table.

    e.g. Select Emp_Id, Emp_Name from Employee.

    I know that i have the table Employee. I have to query and get the column names using those column name i want to do a select query.

    Kindly let me know

    Regards

  3. let me know how i can add 3 & 4 table in one table & how I can transfer data one table to another table ,if i want to include new colunm in a table how the value of that colunm insert predifined table & how we insert a colunm a particular place by using the alter command or other command .

  4. how can i install it on system for database please tell me my boss will kill when he finds out I lied on my application for this job and have no ability to figure even the simplest things out for myself.

  5. please can anybody send me the query for

    Displaying Database from SQL and then list of tables of DYNAMICALLY selected Database?
    please give attention over the word DYNAMICALLY selected……….
    thanx plz mail me as soon as possible….
    thanks………

  6. [email protected] says:

    We can also use like this:

    osql /S ServerName /d databaseName /U Username /P password /i c:\test.sql(location of sql file)

    Thank you

  7. Hi. We had a batch with ISQLW command to run a query in the Query Analyzer of SQL Server 2000. After upgrading to SQL Server 2005 ISQLW cannot be used anymore so we changed it to the recommended command: SQLCMD. But now the query is run in MS-DOS mode and so its result is shown on a black-not-cool screen. Does anyone know how to obtain the result in a Server Management Studio window? Of course, opening the Server Management Studio and paste the query is not a valid answer. The query must be run from the batch. Thanks in advanced.

    1. ZULFIQAR,
      YOU CAN USE BY FOLLOWING SYNTAX :

      C:\>sqlcmd -U -P

      AFTER THIS, YOU ARE CONNECTED WITH YOUR HOSTED SQL SERVER AND YOU CAN DO EVERYTHING BY PASSING COMMAND OF SQL SERVER.

      ALEEMUDDIN SIDDIQUI
      SAP ADMINISTRATOR

Leave a Comment