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

by on December 18, 2006 · 33 comments· LAST UPDATED December 18, 2006

in

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

TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

{ 33 comments… read them below or add one }

1 Suman January 11, 2007 at 7:18 am

How do i connect from sql server query analyzer of one server to query analyzer of another server using sql server command

Reply

2 Telly B March 16, 2007 at 4:58 pm

I Just want to say thanks for having this answer on this article. I did not want to drag each table 1 by 1 in SSMS.

Reply

3 suman May 24, 2007 at 10:49 am

hi ,
how can we know all the tables in a single database in sql 2000.

Reply

4 Balaji Vinu July 9, 2007 at 6:35 am

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

Reply

5 Praveen February 10, 2008 at 1:42 pm

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

Reply

6 Ramees April 4, 2012 at 9:19 am

SP_HELP tablename

Above will give you column details of your table. You can use your mouse to select/copy the column name and paste it where ever you want

Reply

7 Rakesh Kumar March 24, 2008 at 9:21 am

select * from syscolumns where id in (select id from sysobjects where name = ‘Employee’)

Reply

8 awadhesh March 27, 2008 at 10:43 am

how can we know all the tables in a single database in sql 2000.

Reply

9 Ramees April 4, 2012 at 9:22 am

1- For all tables
SELECT * FROM SYS.TABLES

2- For all tables which contain a specific text
SELECT * FROM SYS.TABLES WHERE NAME LIKE ‘%Ramees%’

Reply

10 avn April 23, 2008 at 7:01 am

how to connect sql server from dos command prompt

Reply

11 Bhagwati June 29, 2011 at 12:14 pm

sqlcmd -SCONFROOM-PC1\SQLEXPRESS -UBhagwati -PPal-Q”SELECT * FROM [DBO].[TableName]”

Reply

12 GIRIDHARI July 30, 2008 at 9:45 am

How to connect to another server using sqlcmd?

Reply

13 suneela February 25, 2009 at 5:45 am

i have written a batch backup script to backup Cash register express data base
which is present in sql server which is remote.
how to run this batch file from command line

Reply

14 Ankit Saxena April 1, 2009 at 10:16 am

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 .

Reply

15 Rakesh Saini May 19, 2009 at 7:59 am

how I can see the table in linux please tell me

Reply

16 horny hindu September 24, 2009 at 3:03 pm

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.

Reply

17 subi August 29, 2011 at 8:57 am

I have the connect to data base query,i reply to the please send to my e-mail id

Reply

18 Amir May 21, 2014 at 6:09 am

horny hindu :) sounds like black-is-white :)

Reply

19 Prathamesh November 13, 2009 at 7:25 am

Hi,
Thanks for ur contribution, this page really helped me.

Reply

20 Mike November 23, 2009 at 2:09 am

@Giridhari,

To connect to MS SQL Server (on your local machine), try a line like this one:
“C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.exe” -Shostname\sqlservername -E

More information on the parameters for SQLCMD.exe here: http://www.databasejournal.com/features/mssql/article.php/3654176/SQL-Server-2005-Command-Line-Tool-SQLCMD–Part-I.htm

Reply

21 Aginesh Kumar December 8, 2009 at 2:31 pm

Ihow can retive database connect with sql server Connect by cmd

Reply

22 mohan February 5, 2010 at 7:11 am

hi sir/madam

if user deleted the existing (default) tables in the database, how to retrive emp table once agin, what is the command can u mail to my id,plsss

Reply

23 Tapan Lonkar March 18, 2010 at 5:44 am

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………

Reply

24 Amitha June 11, 2010 at 7:47 am

Very useful! thanks!

Reply

25 dalu November 6, 2010 at 6:26 am

how can i create tables and fields in SQL?

Reply

26 thiyagi January 18, 2011 at 6:49 pm

thanks..

Reply

27 Siva January 27, 2011 at 5:28 am

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…:-)

Reply

28 siva.sanniyasi@gmail.com January 27, 2011 at 7:22 am

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

Reply

29 SQL_2005 June 3, 2011 at 6:51 am

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.

Reply

30 mrityunjay singh August 26, 2011 at 7:31 am

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

Reply

31 arash November 26, 2012 at 8:36 am

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

1>sp_help
2>go

Reply

32 Sarit Hod January 29, 2014 at 4:40 am

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

Reply

33 Matias Colli May 29, 2014 at 8:19 pm

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

Reply

Leave a Comment

Tagged as:

Previous Faq:

Next Faq: