Connection.GetSchema

[Jiho Han]

I am trying to test for the existence of a particular table in all databases
on a server.

I found some samples and looking at the SDK doc as well, I found the following
use of GetSchema method on Connection class:

SqlConnection.GetSchema(string collectionName, string[] restrictions)

So, I would do something like,

DataTable dt = connection.GetSchema("Tables", new string[] {null, null, "MyTable"});

which returns nothing.

Curiously enough, if I try the OleDbConnection instead of SqlConnection,
it returns results that I expect. I am guessing that there is a difference
in how GetSchema is implemented for SqlConnection and OleDbConnection.

Any ideas?
Thanks

Jiho

 

[Dana King]

To check for a table in a sql server database use the sp_tables stored
procedure.
The following example iterates through all tables and uses a datareader
object.

If you need to first get a list of all databases on the server, use the
sp_databases stored procedure first and iterate through that, for each
database that exists, check for the existence of your tables with the code
below.

----------

Try
Dim sqlConn As New
SqlConnection("Server=servername;Database=db_name;Trusted_Connection=True")

Dim cmd As New SqlCommand("sp_tables", sqlConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@table_name", "%")
cmd.Parameters.AddWithValue("@table_owner", "dbo")

Dim dr As SqlDataReader
sqlConn.Open()
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While dr.Read
'== do something with the table name here
dr.Item("TABLE_NAME").ToString
End While

dr.Close()

sqlConn.Dispose()

Catch ex As Exception

If sqlConn.State = ConnectionState.Open Then sqlConn.Close()
sqlConn.Dispose()
MessageBox.Show(ex.Message)

End Try

 

[Jiho Han]

Thanks for your response but I already know about sp_tables/sp_databases.
I was trying to use GetSchema to support multiple platforms including Oracle.

Thanks

To check for a table in a sql server database use the sp_tables stored
procedure.
The following example iterates through all tables and uses a
datareader
object.
If you need to first get a list of all databases on the server, use
the sp_databases stored procedure first and iterate through that, for
each database that exists, check for the existence of your tables with
the code below.

----------

Try
Dim sqlConn As New
SqlConnection("Server=servername;Database=db_name;Trusted_Connection=T
rue")
Dim cmd As New SqlCommand("sp_tables", sqlConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@table_name", "%")
cmd.Parameters.AddWithValue("@table_owner", "dbo")
Dim dr As SqlDataReader
sqlConn.Open()
dr =
cmd.ExecuteReader(CommandBehavior.CloseConnection)
While dr.Read
'== do something with the table name here
dr.Item("TABLE_NAME").ToString
End While
dr.Close()

sqlConn.Dispose()

Catch ex As Exception

If sqlConn.State = ConnectionState.Open Then
sqlConn.Close()
sqlConn.Dispose()
MessageBox.Show(ex.Message)
End Try

I am trying to test for the existence of a particular table in all
databases on a server.

I found some samples and looking at the SDK doc as well, I found the
following use of GetSchema method on Connection class:

SqlConnection.GetSchema(string collectionName, string[] restrictions)

So, I would do something like,

DataTable dt = connection.GetSchema("Tables", new string[] {null,
null, "MyTable"});

which returns nothing.

Curiously enough, if I try the OleDbConnection instead of
SqlConnection, it returns results that I expect. I am guessing that
there is a difference in how GetSchema is implemented for
SqlConnection and OleDbConnection.

Any ideas?
Thanks
Jiho