Retrieving SQL Server Table Schema

[grimmettm]

Can anyone please tell me how to retrieve all tables in an SQL server database. I know that it can be done with an OleDB connection using the GetOleDbSchemaTable method. But the GetSchemaTable method for the SqlConnection only returns column schema. Any help is greatly appreciated. Thanks!

 

Cheers!~ :D

[grimmettm]

I found the answer! You CAN'T get the table schema from SQL Server using the GetSchemaTable function. In order to list all of the FIELDS in a table, you use the GetSchemaTable function. In order to list all tables in a given database, I found that selecting the NAME from sysobjects table where xtype = 'n' would retrieve all tables in the desired database. the xtype column is used to identify databases. This may be unique to each useage, but it is a starting point!

 

Cheers!~

[Robby]

You can use these objects in Query Analyzer, filter by Type or xType

 

(In Master) SELECT * from sysdatabases

(In Master and your DB) SELECT * from sysobjects

[mavel]

Yes, but when I want to get all the tables I use antoher where condition: type='U'.

 

I think it's safer.

[Robby]

Isn't that what I said "...filter by Type or xType"

[MyGeneration]

Yes, use the INFORMATION_SCHEMA VIEWS, IE,

 

SELECT * from INFORMATION_SCHEMA.SCHEMATA

 

 

MyGeneration Software

We are the meta-data experts

http://www.mygenerationsoftware.com/