Re: Connection.GetSchema
- From: "Dana King" <bushido101@xxxxxxxxxxx>
- Date: Sun, 16 Apr 2006 19:47:51 -0300
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" <jihohan@xxxxxxxxx> wrote in message
news:ed1a6e0222b8c82df17b264f92@xxxxxxxxxxxxxxxxxxxxx
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
.
- Follow-Ups:
- Re: Connection.GetSchema
- From: Jiho Han
- Re: Connection.GetSchema
- References:
- Connection.GetSchema
- From: Jiho Han
- Connection.GetSchema
- Prev by Date: Re: Using @@Identity with access
- Next by Date: OracleConnection.BeginTransaction() calls OracleCommand.ExecuteNonQuery() internally?
- Previous by thread: Connection.GetSchema
- Next by thread: Re: Connection.GetSchema
- Index(es):
Relevant Pages
|
