Connection.GetSchema

14 Apr 2006 7:44 PM

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

16 Apr 2006 10:47 PM

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

Show quote

"Jiho Han" <jiho***@yahoo.com> wrote in message
news:ed1a6e0222b8c82df17b264f92@news.microsoft.com...
>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
>
>

17 Apr 2006 6:29 PM

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

Show quote

> 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
>
> "Jiho Han" <jiho***@yahoo.com> wrote in message
> news:ed1a6e0222b8c82df17b264f92@news.microsoft.com...
>
>> 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
>>