?
Solved

How to get all the table in a database?

Posted on 2001-09-18
4
Medium Priority
?
376 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 80 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.
Suggested Courses

770 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