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

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

Advertisement

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

πŸ₯Ί Was this helpful? Please add a comment to show your appreciation or feedback.

nixCrat Tux Pixel Penguin
Hi! 🀠
I'm Vivek Gite, and I write about Linux, macOS, Unix, IT, programming, infosec, and open source. Subscribe to my RSS feed or email newsletter for updates.

38 comments… add one
  • thiyagi Jan 18, 2011 @ 18:49

    thanks..

  • Siva Jan 27, 2011 @ 5:28

    This is a good article, but it is not good enough to how to connect SQL through command prompt?

    thanks for the article. keep it up…:-)

  • siva.sanniyasi@gmail.com Jan 27, 2011 @ 7:22

    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

  • SQL_2005 Jun 3, 2011 @ 6:51

    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.

  • mrityunjay singh Aug 26, 2011 @ 7:31

    when i am trying to connect sql server through command prompt i am unable to connect it ….

    • ALEEMUDDIN SIDDIQUI Jan 26, 2015 @ 6:59

      I THINK YOU ARE USING YOUR OWN DESKTOP WHILE IT IS NECESSARY TO CONNECT USING REMOTE SERVER DESKTOP TO CONNECT PROPERLY IN SQL SERVER THROUGH COMMAND PROMPT

  • arash Nov 26, 2012 @ 8:36

    i should recall that after sp_…. you should use go command to run.for exapmle:

    1>sp_help
    2>go

  • Sarit Hod Jan 29, 2014 @ 4:40

    Can you please mention SQLS*Plus – this is a great free command line tool. There is command there “show tables” that lists all tables.

  • Matias Colli May 29, 2014 @ 20:19

    I didn’t know that sp_databases as “SHOW DATABASE” ANSI SQL.
    Thanks.
    Matias Colli
    Perito Judicial en InformΓ‘tica

  • zulfiqar ali Jan 26, 2015 @ 9:18

    how can i used sql through cmd

    • ALEEMUDDIN SIDDIQUI Jan 30, 2015 @ 6:10

      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 Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre> for code samples. Your comment will appear only after approval by the site admin.