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 |
|
gdeconto
Posting Yak Master
107 Posts |
 Posted - 2002-04-04 : 18:07:33
|
| 1. I have a sql2k server (call it sql2kserver)2. I have a second sql2k server (call it sql2kserver2)I am able to connect both sql2kserver2 to sql2kserver as a linked server. I can enumerate the databases on sql2kserver2, so at least I know what databases are available.I would like to be able to get a list of the tables and/or views, as well as what columns they contain, via a stored procedure.The docs don't really describe this, and haven't found any references to this in the forums on this site. Questions:1. does anyone know how to do this?2. if the linked server were for a non-sql oledb database, would we be able to get this same info?All help appreciated (as always). |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-04 : 18:14:11
|
| The ANSI standard for the SQL language defines the INFORMATION_SCHEMA views for this purpose. There is an INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS views that you can query:SELECT * FROM linkedserver.database.INFORMATION_SCHEMA.TABLESSELECT * FROM linkedserver.database.INFORMATION_SCHEMA.COLUMNSI'm not sure whether an ANSI-compliant product MUST provide these views, however I'm pretty sure most of them do. SQL Server 7.0 and higher supports them (Books Online has more information on these and other INFORMATION_SCHEMA views) |
 |
|
|
gdeconto
Posting Yak Master
107 Posts |
Posted - 2002-04-08 : 19:30:34
|
| thx, Rob.had no trouble with getting the tables and columns for a local database but had some difficulties with using it on linked servers.Will look at it again and let you know.thx |
|
|
|
|
|
|
how to enum tables/columns from linked serve