• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

How to get all the table in a database?

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
superleo
Asked:
superleo
  • 2
1 Solution
 
mironCommented:
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
 
superleoAuthor Commented:
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
 
nigelrivettCommented:
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
 
mironCommented:
yep,
sp_helpcolumns is my own system procedure, woops. But sp_help <table_name> gives column names, too :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now