Skip to main content

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

Comments

Post a Comment

Popular posts from this blog

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...
Post a Comment
Read more

ASP.NET MVC - Set custom IIdentity or IPrincipal

Here's how I do it. I decided to use IPrincipal instead of IIdentity because it means I don't have to implement both IIdentity and IPrincipal. Create the interface interface ICustomPrincipal : IPrincipal { int UserId { get ; set ; } string FirstName { get ; set ; } string LastName { get ; set ; } } CustomPrincipal public class CustomPrincipal : ICustomPrincipal { public IIdentity Identity { get ; private set ; } public bool IsInRole ( string role ) { return false ; } public CustomPrincipal ( string email ) { this . Identity = new GenericIdentity ( email ); } public int UserId { get ; set ; } public string FirstName { get ; set ; } public string LastName { get ; set ; } } CustomPrincipalSerializeModel - for serializing custom information into userdata field in FormsAuthenticationTicket object. public class CustomPrincipalSerializeMode...
8 comments
Read more

Validate credit card number with Mod 10 algorithm in C#

Introduction All you know what information contains in your NIC number. But do you know what information contains in the Credit Card Number? Following article provides brief details about what information contain in your credit card and demonstrates to how to validate credit card number using mod 10 (Luhn) algorithms with C#. Background  Card Length   Typically, credit card numbers are all numeric and the length of the credit card number is between 12 digits to 19 digits.  14, 15, 16 digits – Diners Club 15 digits – American Express 13, 16 digits – Visa 16 digits - MasterCard   For more information please refer  http://en.wikipedia.org/wiki/Bank_card_number . Hidden information  Major Industry Identifier (MII)   The first digit of the credit card number is the Major Industry Identifier (MII). It designates the category of the entry which issued the card.     1 and 2 – Airlin...
1 comment
Read more