Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vicki
Posting Yak Master
117 Posts |
 Posted - 2002-04-17 : 10:01:43
|
| Hi,Is there any way to list all the tables in the datatabae?Thanks |
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-04-17 : 10:05:47
|
| SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLESThe above query will return all the table names from the database. You can use OBJECTPROPERTY to filter system tables. You can also filter on the TABLE_SCHEMA column for tables owned by specific users.--HTH,VyasCheck out my SQL Server site @http://vyaskn.tripod.com |
 |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-04-17 : 10:30:03
|
| This will also return views. If you're just looking for tables be sure to add a where clause.SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'Jeff BanschbachConsultant, MCDBA |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-04-17 : 10:32:25
|
| Right, I forgot the 'BASE TABLE' check.--HTH,VyasCheck out my SQL Server site @http://vyaskn.tripod.com |
|
|
|
vicki
Posting Yak Master
117 Posts |
Posted - 2002-04-17 : 15:59:32
|
| it's work and thank you very very much. It was work for SQL 2000 and not for SQL 6.5 rite?, I try to run it in SQL 6.5 then I got the error message 'Invalid object name 'INFORMATION_SCHEMA.TABLES'How can I make it work in 6.5?Thanks |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-17 : 16:03:34
|
| Unfortunately the INFORMATION_SCHEMA views were not available in 6.5, so you'll have to stick with querying the system tables. Unless you feel brave, you could copy the INFORMATION_SCHEMA view definition from SQL 2000 and create it in 6.5, it should work but will probably need some modification. |
|
|
|
|
|
|
tables