SQL Query to list down the tables and all coumhs in database,

Here is a query which can fetch all the table names and all the colunm names in each table.

select table_name, column_name, data_type, character_maximum_length, is_nullable from information_schema.columns where table_name in (select name from sysobjects where xtype='U') order by table_name

Comments

Friday, April 16, 2004 11:12 AM by M. Keith Warren

# re: SQL Query to list down the tables and all coumhs in database,

Much easier way...

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Monday, August 27, 2007 2:40 AM by Rajeev

# re: SQL Query to list down the tables and all coumhs in database,

This query is not working in sapdb. any help will be appreciated..

Tahnks & Regards

Rajeev

Monday, October 15, 2007 8:01 AM by shailesh

# re: SQL Query to list down the tables and all coumhs in database,

Please give me fast comment

Thursday, November 22, 2007 9:51 AM by Vinod

# re: SQL Query to list down the tables and all coumhs in database,

Above query is not working in TOAD( Databse managemnet tool)any help will be appreciated.

Thanks

Vinod

Thursday, November 29, 2007 5:16 AM by Mohan

# re: SQL Query to list down the tables and all coumhs in database,

Hi,

Am creating a page with JSP/SQL. I have a list of all tables. i need to display all the fields of each tables. What is the SQL Query? OR Any other chance to do it??

Thursday, December 13, 2007 9:39 AM by Raju

# re: SQL Query to list down the tables and all coumhs in database,

select * from user_indexes where tablename='ABC';

Wednesday, February 27, 2008 6:53 AM by sanjay

# re: SQL Query to list down the tables and all coumhs in database,

one can also use sp_help tablename in sql

Wednesday, April 30, 2008 1:18 PM by Mel Wolinsky

# re: SQL Query to list down the tables and all coumhs in database,

Good answer, Sanjeeb!!!

The "SELECT * FROM INFORMATION_SCHEMA.COLUMNS"

Simple enough that even I could do it!!!

I've tried other resources, and got a headache,

and any site with 'MSDN' in it would put me

into Quagmire city.

Keep up the good work.

Monday, June 02, 2008 2:38 AM by Suja

# re: SQL Query to list down the tables and all coumhs in database,

SELECT * FROM INFORMATION_SCHEMA.COLUMNS is not working in SQL server

Monday, July 07, 2008 9:55 PM by Paing Thu

# re: SQL Query to list down the tables and all coumhs in database,

SELECT * from syscolumns

WHERE id = object_id('yourtablename')

Tuesday, September 02, 2008 9:31 AM by sudhir

# re: SQL Query to list down the tables and all coumhs in database,

Can any One help me with the query which can give me the Tablename's and Colums names in which has a Specific Data.

Please.

Friday, September 05, 2008 1:27 AM by jagadheesan

# re: SQL Query to list down the tables and all coumhs in database,

SELECT * FROM table_one WHERE unique_column =

(SELECT unique_column FROM table_two WHERE id_column = 1)

Sunday, September 07, 2008 3:37 AM by Sandip

# re: SQL Query to list down the tables and all coumhs in database,

SQL Query to list down the tables in database,

Wednesday, September 10, 2008 5:47 AM by Vampire

# re: SQL Query to list down the tables and all coumhs in database,

@Rajeev :

The SELECT * FROM master.INFORMATION_SCHEMA.COLUMNS is meant for YUKON.. if you are working on SQL Server 2000 this query will not help.

The equivalent query for 2000 is SELECT * FROM master..SysColumns.

Friday, September 12, 2008 3:28 AM by Sameer

# re: SQL Query to list down the tables and all coumhs in database,

Thank You.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

it works nice.

Wednesday, October 15, 2008 3:34 AM by anwar

# re: SQL Query to list down the tables and all coumhs in database,

Good jobs

thanks

Friday, February 13, 2009 7:17 AM by yuvika

# re: SQL Query to list down the tables and all coumhs in database,

hi,

can anyone tell me what is the SQL Query to list down all the tables from database

iam very new to this environment.

Any help will be appreciated.

Thanks in advance.

Friday, February 13, 2009 11:34 PM by raja

# re: SQL Query to list down the tables and all coumhs in database,

plz i need urgently

Friday, February 27, 2009 6:07 PM by Prabhu

# re: SQL Query to list down the tables and all coumhs in database,

select * from all_tables

will list all the tables that the current user (with which you logged in) has access to.

select * from all_tables where owner = <schema_name>

will give the list of tables for the mentioned schema name (if you have access to it)

Wednesday, April 15, 2009 5:31 AM by chandu

# re: SQL Query to list down the tables and all coumhs in database,

Good answer Keith, it works in sqlserver.

thank u.

Monday, May 18, 2009 1:35 AM by Ravi Ranjan

# re: SQL Query to list down the tables and all coumhs in database,

Only Required to ListDown TableNAme

Monday, May 18, 2009 8:48 AM by swati

# re: SQL Query to list down the tables and all coumhs in database,

hi nice work....

code worked well to me

Saturday, May 23, 2009 9:54 AM by vivek

# re: SQL Query to list down the tables and all coumhs in database,

It's Working, Thanking U.

Saturday, October 03, 2009 3:22 AM by Onkar Ahire

# re: SQL Query to list down the tables and all coumhs in database,

Thanks for showing me Query...

Friday, February 12, 2010 10:16 AM by Renga

# re: SQL Query to list down the tables and all coumhs in database,

select * from user_tables

select * from all_tables

Try this...

Wednesday, March 17, 2010 3:27 AM by niC

# re: SQL Query to list down the tables and all coumhs in database,

To show all tables in a database:

$showTables = mysql_query("SHOW TABLES FROM $database");

To show the columns in a selected table in the database:

$fields = mysql_query("SHOW COLUMNS FROM $TableName");

PHP is used in here, but still the queries are working.

However, is there a single query that shows all columns or fields in a database without selecting a specific table anymore?

Monday, March 29, 2010 3:41 AM by Akeju

# re: SQL Query to list down the tables and all coumhs in database,

I need help. looking for code in SQL to show the list of the detail of and items in a specific table

Sunday, April 11, 2010 3:32 AM by chandra kanth

# re: SQL Query to list down the tables and all coumhs in database,

select * from tab;

Thursday, April 15, 2010 12:45 AM by Jay

# re: SQL Query to list down the tables and all coumhs in database,

select * from user_objects where object_type = 'TABLE'

Friday, May 28, 2010 7:35 AM by Rajesh Kumar

# re: SQL Query to list down the tables and all coumhs in database,

Try this one,

select * from sys.tables

Friday, August 27, 2010 12:54 AM by neethu

# re: SQL Query to list down the tables and all coumhs in database,

SELECT distinct table_name FROM INFORMATION_SCHEMA.COLUMNS

This query will retrieve the table names

Tuesday, September 07, 2010 12:51 AM by Soumya Patel

# re: SQL Query to list down the tables and all coumhs in database,

Thanks the query is worked for me to get the tables name in SQL

Tuesday, September 07, 2010 6:40 AM by Shailesh Deshmukh

# re: SQL Query to list down the tables and all coumhs in database,

SELECT distinct table_name FROM INFORMATION_SCHEMA.COLUMNS

this is the correct qurey.this qurey shows exact

tables in the database

Tuesday, January 18, 2011 9:33 AM by saravanan,madurai

# re: SQL Query to list down the tables and all coumhs in database,

hi i am using asp.net with sqlserver ,i have more database in contain folder . i need to display all database into dropdownlistbox how this possible

pls help me

Tuesday, February 15, 2011 11:02 AM by Chandan Choudhary

# re: SQL Query to list down the tables and all coumhs in database,

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

SELECT * FROM INFORMATION_SCHEMA.TABLES

easy and simple

Thursday, March 31, 2011 6:32 AM by kumar

# re: SQL Query to list down the tables and all coumhs in database,

Try this

Select * from [dbname].[dbo].[tablename]

This works for me :-)

Saturday, April 30, 2011 12:10 AM by Neo

# re: SQL Query to list down the tables and all coumhs in database,

try this

select * from user_tab_columns

Saturday, April 30, 2011 12:13 AM by Neo

# re: SQL Query to list down the tables and all coumhs in database,

try this

select * from user_tab_columns

Friday, June 10, 2011 1:34 PM by me

# re: SQL Query to list down the tables and all coumhs in database,

i want to execute a query on the whole database how i can do it?

Wednesday, July 20, 2011 2:23 AM by Hara

# re: SQL Query to list down the tables and all coumhs in database,

How to get the tables count and name in one particular dsn/mdb using SQL Query, Please help

Friday, July 22, 2011 10:09 AM by Scott

# re: SQL Query to list down the tables and all coumhs in database,

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

worked for me, thanks. I'm no SQL expert and a vendor was telling me they couldn't get me a list of the fields in their product's database and that I should find some third-party utility. Nope. I was on the phone but I wish I could've seen their faces when I dumped the query results to a CSV and shared.

Friday, August 12, 2011 7:31 AM by Purushothaman

# re: SQL Query to list down the tables and all coumhs in database,

Thankyou so much for this post.Great Question and correct answer.

Wednesday, August 31, 2011 10:14 AM by ANSHUL

# re: SQL Query to list down the tables and all coumhs in database,

need a query to show all the databases on perticular instance of SQL Server

Friday, September 23, 2011 12:12 PM by Ravi

# re: SQL Query to list down the tables and all coumhs in database,

This link provides SQL query to display all columns with datatypes for a given Table name

www.besttechtools.com/SQLArticles.aspx

Saturday, September 24, 2011 5:24 AM by pritam

# re: SQL Query to list down the tables and all coumhs in database,

use YourDB go select * from sys.tables

Friday, October 14, 2011 1:12 AM by Mae

# re: SQL Query to list down the tables and all coumhs in database,

"select * from table '" & txtTable.text & "'",

can i use this code??

Friday, October 14, 2011 1:15 AM by Mae

# re: SQL Query to list down the tables and all coumhs in database,

"select * from table '" & txtTable.text & "'",

can i use this code??

Wednesday, October 19, 2011 1:48 PM by Raji

# re: SQL Query to list down the tables and all coumhs in database,

I have tried in sql server 2008, and this is how it worked for me..

1) Select the required database.

2) Execute this query :

SELECT * FROM INFORMATION_SCHEMA.TABLES

Tip/Trick: Fix Common SEO Problems Using the URL Rewrite Extension

Search engine optimization (SEO) is important for any publically facing web-site. A large % of traffic to sites now comes directly from search engines, and improving your site’s search relevancy will lead to more users visiting your site from search engine queries. This can directly or indirectly increase the money you make through your site. This blog post covers how you can use the free Microsoft URL Rewrite Extension to fix a bunch of common SEO problems that your site might have. It takes less than 15 minutes (and no code changes) to apply 4 simple URL Rewrite rules to your site, and in doing so cause search engines to drive more visitors and traffic to your site. The techniques below work equally well with both ASP.NET Web Forms and ASP.NET MVC based sites. They also works with all versions of ASP.NET (and even work with non-ASP.NET content). [In addition to blogging, I am also now using Twitter for quick updates and to sh...

.NET, MVC, C#.NET, ASP.NET , Jquery, SQL, Kendo UI