Posted by Omar Abid on 06/10/07 23:02

On 10 juin, 11:06, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Omar Abid (omar.abid2...@gmail.com) writes:
> > Im using VB 2005 to create a program that open SQL Data base
> > The problem that i want to detect the tables of a database
> > so how can i know a data base tables instantly
>
> Your question is very open-ended and it is not clear what you really
> want assistance with. Is it running a metadata query in SQL Server? Or
> is about data access from VB .Net in general? In the latter case, I would
> suggest that you are better off by first learning the basics before you
> play with metadata.
>
> Nevertheless, a query you could run to get all tables is this one:
>
> SELECT quotename(schema_name(schema_id)) + '.' + quotename(name)
> FROM sys.tables
> ORDER BY 1
>
> As for running it from VB - there are many possible variations depending
> on what you want to do with the data. Here is a console-mode prorgam that
> just prints the table names, and which uses DataAdapater.Fill. It also
> includes some error handling.
>
> Imports System.Data
> Imports System.Data.Sql
> Imports System.Data.SqlClient
>
> Module Bugtest
>
> Private Sub ErrorFill(ByVal sender as Object, ByVal args as FillErrorEventArgs)
> Console.WriteLine(args.Errors.Message)
> args.Continue = true
> End Sub
>
> Private Sub PrintSqlErrors(ByVal errors As SqlErrorCollection, ByVal what As String)
> Dim e As SqlError
> For Each e In errors
> Console.Write (Now.ToString("HH:mm:ss.fff") & " " & what & _
> " Message: Msg " & e.Number.ToString() & _
> ", Severity " & e.Class.ToString() & _
> ", State: " & e.State.ToString() & _
> ", Procedure: " & e.Procedure & _
> ", Line no: " & e.LineNumber.ToString & vbCrLf & _
> e.Message & vbCrLf)
> Next
> End Sub
>
> Private Sub OutputException(ex As Exception)
> If TypeOf ex Is SqlException Then
> Dim SqlEx As SqlException = DirectCast(ex, SqlException)
> PrintSqlErrors(SqlEx.Errors, "Error")
> Else
> Console.WriteLine(ex.ToString())
> End if
> End Sub
>
> Private Sub SqlInfoMessage(ByVal sender As Object, ByVal e As SqlInfoMessageEventArgs)
> PrintSqlErrors(e.Errors, "INFO MSG")
> End Sub
>
> Private Sub PrintDataTable(ByVal tbl As DataTable)
> Console.Writeline ("=========================================================" & vbCrLf)
> For Each col As DataColumn In tbl.Columns
> Console.Writeline (col.ColumnName & vbTab)
> Next col
> Console.Writeline (vbCrLf)
> For Each row As DataRow In tbl.Rows
> For Each col As DataColumn In tbl.Columns
> Console.Writeline (row(col).ToString() & vbTab)
> Next col
> Console.Writeline(vbCrLf)
> Next row
> End Sub
>
> Public Sub Main()
>
> Dim cn As New SqlConnection, _
> strConn As String
>
> ' This does not help.
> ' AddHandler cn.InfoMessage, AddressOf SqlInfoMessage
>
> ' Connection string, change server and database!
> strConn = "Application Name=systablesdemo;Integrated Security=SSPI;"
> strConn &= "Data Source=(local);Initial Catalog=AdventureWorks"
>
> Try
> cn.ConnectionString = strConn
> cn.Open()
> Catch ex As Exception
> Console.Writeline(ex.Message, "Connection failed!")
> cn = Nothing
> Exit Sub
> End Try
>
> Dim cmd As SqlCommand = cn.CreateCommand()
>
> cmd.CommandText = "SELECT quotename(schema_name(schema_id)) + "
> cmd.CommandText &= " '.' + quotename(name) FROM sys.tables "
> cmd.CommandText &= "ORDER BY 1"
> Dim dt As New DataTable, _
> da As SqlDataAdapter = New SqlDataAdapter(cmd), _
> no_of_rows As Integer
> AddHandler da.FillError, AddressOf ErrorFill
> Try
> no_of_rows = da.Fill(dt)
> Catch e As Exception
> OutputException(e)
> End Try
> Console.Writeline("No of rows filled " & no_of_rows.ToString() & vbCrLf)
> PrintDataTable(dt)
>
> cn.Close()
> cn.Dispose()
>
> End Sub
>
> End Module
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thank u for the your reply and the links.
What I really want is too easy and clear :
I create a program that open SQL 2005 Data Bases and show tables of
the data base.
What I have done is to get a table data but I have to know the table
name before opening any one.
What I want is to know the tables name of the current data base.
Any idea ?
Omar Abid

[Back to original message]