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

C# Generic class to parse value - "GenericConverter"

    public class GenericConverter     {         public static T Parse<T>(string sourceValue) where T : IConvertible         {             return (T)Convert.ChangeType(sourceValue, typeof(T));         }         public static T Parse<T>(string sourceValue, IFormatProvider provider) where T : IConvertible         {             return (T)Convert.ChangeType(sourceValue, typeof(T), provider);         }     }     public static class TConverter     {         public static T ChangeType<T>(object value)         {             return (T)ChangeType(typeof(T), value);         }         public static object ChangeType(Type t, object value)         {             TypeConverter tc = TypeDescriptor.GetConverter(t);             return tc.ConvertFrom(value);         }         public static void RegisterTypeConverter<T, TC>() where TC : TypeConverter         {             TypeDescriptor.AddAttributes(typeof(T), new TypeConverterAttribute(typeof(TC)));         }     } ----------------
Post a Comment
Read more

How to create a countdown timer in jquery

Create a countdown timer in jQuery First we need to include the jQuery library file to the HTML page to perform this task. To do that we need to understand that what exactly a jQuery library fie is ? JQuery library file is the library of JavaScript, which means this file contains the predefined functions of jQuery. We just need to call these functions to perform the task. jQuery functions reduces the lines of code and makes our task easy. As this jQuery library file contains the javascript functions so we need to call the function within <script> </script> tag. Now after including the file, we need to define a variable which will store that for how long you want the timer on the page(c=60) and now the time you set needs to be changed in hours , minutes and seconds using the code “ var hours = parseInt( time / 3600 ) % ;var minutes = parseInt( time / 60 ) % 60; var seconds = time % 60;” Now we need to put the condition if timer got finished (if (t
1 comment
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 – Airlines  3 – Travel 4 and 5 – Banking and Financial 6 – Merchandising and Banking/Fina
1 comment
Read more