Solved

How to get all the table in a database?

Posted on 2001-09-18
4
371 Views
Last Modified: 2012-05-04
Hello all,

I would like to know that how can I run a SQL statement to get all user table in a database. By the way, I also want to know how to get all fields name in a specific table. Thank you for your help.

Yours
Leo ^,^
0
Comment
Question by:superleo
  • 2
4 Comments
 
LVL 9

Expert Comment

by:miron
ID: 6489608
sp_help <table_name> wil tell everything about table, including columns
sp_helpcolumns <table_name> will only be concerned with columns.

select [name] from database_name.dbo.sysobjects where
OBJECTPROPERTY( id, 'IsUserTable' ) = 1

will return names of all user tables in the database.

select table_name from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'

will do the same.
0
 

Author Comment

by:superleo
ID: 6489776
Thank you miron,

Thanks for your help. Besides, I would like to know that how can I retrieve all fields name for a specific by using a SQL statement. Really thanks for your help.

Yours
Leo
0
 
LVL 18

Accepted Solution

by:
nigelrivett earned 20 total points
ID: 6490060
select TABLE_NAME from information_schema.tables
gives all tables

select COLUMN_NAME from information_schema.columns where TABLE_NAME = 'mytable'
gives the columns.

Or you can go directly to the system tables (not recommended for future comapatibility)

tbls
select name from sysobjects where xtype = 'U'
cols
select name from syscolumns where id = (select id from sysobjects where name = 'tblname' and xtype = 'U')
or
select name from syscolunms where id = object_id('mytbl')
0
 
LVL 9

Expert Comment

by:miron
ID: 6490852
yep,
sp_helpcolumns is my own system procedure, woops. But sp_help <table_name> gives column names, too :)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question