How to get list of all tables in MS SQL Server?

select TABLE_NAME from information_schema.tables where Table_Type = 'BASE TABLE'


Posted on Wednesday, March 12, 2008 by | Comments (15) | Add Comment

Comments

Gravatar

Exactly what I was looking for

Thanks guys this is exactly what I was looking for.

Posted on 6/24/2008 2:19:48 AM by Timothy Kruger #
Gravatar

Re:How to get list of all tables in MS SQL Server?

Hi, I am tracing data on application which generated temp tables into TempDB. How can use "select statement" to display these data whereby having table name start with dbo.#xxxxx?
Thanks.

Posted on 3/5/2009 6:37:35 PM by Joan Lim #
Gravatar

Joan,
I can honestly say that I don't know.

Gravatar

Re:How to get list of all tables in MS SQL Server?

Hi,
use the below to list all the tables:
sp_tables

Posted on 5/28/2009 9:22:20 PM by Aravindhan #
Gravatar

Re:How to get list of all tables in MS SQL Server?

How to get list of all tables and detail about the table (eg: owner name ,view ,triger ,identity,pinned ,filegroup, no of rows etc) in MS SQL Server?

Thanks in advance

Posted on 5/28/2009 9:25:17 PM by aravindhan #
Gravatar

Try following queries, may be you can find something useful in there:

select * from information_schema.COLUMNS
select * from information_schema.KEY_COLUMN_USAGE
select * from information_schema.TABLE_CONSTRAINTS

Gravatar

Re:How to get list of all tables in MS SQL Server?

In SQL Server 2008.

select * from sys.all_objects where type = 'U'

Posted on 8/20/2010 8:25:59 AM by Chirag Nirmal #
Gravatar

Re:How to get list of all tables in MS SQL Server?

use DatabaseName
get all table information with Sql Server
sp_tables

Posted on 10/21/2010 4:37:28 AM by Narendra #
Gravatar

Re:How to get list of all tables in MS SQL Server?

try this
select TABLE_NAME,Table_Type from information_schema.tables
where Table_Type = 'BASE TABLE'

Posted on 2/16/2011 1:03:11 PM by hammad qureshi #
Gravatar

What's wrong with it? This is what I wrote in a post.

Gravatar

Re:How to get list of all tables in MS SQL Server?

I have a MS SQL on server 2003. I have an extremely large list of tables and I am trying to find a table with a specific word in the table name. bar or barcodes. what is the proper t-sql for finding a table with bar or barcode in the table name?

Posted on 3/3/2011 9:43:04 AM by Rob #
Gravatar

select TABLE_NAME from information_schema.tables where Table_Type = 'BASE TABLE' and TABLE_NAME like '%bar%'

Gravatar

Re:How to get list of all tables in MS SQL Server?

Sorry I am new to SQL... What command I can use to find any specfic word in SQL Database?

Posted on 4/7/2011 9:08:00 AM by Dave #
Gravatar

You can use LIKE to search for a word in specific table/column.

Gravatar

Re:How to get list of all tables in MS SQL Server?

Can you please give me an example?

Posted on 4/7/2011 9:16:03 AM by Dave #
Gravatar

SELECT * FROM suppliers
WHERE supplier_name like '%bob%';

Gravatar

Re:How to get list of all tables in MS SQL Server?

Can we put wildcard for colum... if yes, than how?

Posted on 4/7/2011 9:18:27 AM by Dave #
Gravatar

No you can't.

Gravatar

Re:How to get list of all tables in MS SQL Server?

Is below statement will not work?

SELECT * FROM TABLE_NAME where '' like '%hello123%'

Posted on 4/7/2011 9:37:15 AM by Dave #
Gravatar

No. It won't.

Gravatar

Re:How to get list of all tables in MS SQL Server?

finding some good concept........

Posted on 12/17/2011 11:10:28 AM by yjreddy #
Gravatar

Re: How to get list of all tables in MS SQL Server?

select * from sysobjects where xtype='u' and like '%bob%'

Posted on 2/9/2012 10:50:44 PM by komalkumar #
Gravatar

Re: How to get list of all tables in MS SQL Server?

hii..
i m new to SqlServer.
I want the Query for finding all the tables from a particular database.

Posted on 12/18/2012 2:38:39 AM by Ramakanta Behera #

New Comment

Your Name:
Email (for internal use only):
Comment:
 
Code above:

Categories

Valid HTML5