Solved

How to get all the table in a database?

Posted on 2001-09-18
4
369 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
yep,
sp_helpcolumns is my own system procedure, woops. But sp_help <table_name> gives column names, too :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 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

7 Experts available now in Live!

Get 1:1 Help Now