Solved

How to get all the table in a database?

Posted on 2001-09-18
4
370 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

930 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now