[.net_Apprentice]

Hi guys:
Need to check fron VB.net if a Table exists in my SQL (local) Server.
Thanks in advance
Thanks

[_HAWK_]

Why do you need this from code? Do you have SSMS to open the DB and edit/create/drop tables on your server instance?

[kai_itz me]

IF U HAVE SSMS to open the DB and edit/create/drop tables on your server instance THEN ONLY USE BELOW CODE

THIS WILL TELL U ALL THE TABLE AS WELL AS SCHEMA PRESENT IN YOUR DATABASE

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%emp_code%'
ORDER BY schema_name, table_name

This post has been edited by kai_itz me: 13 January 2013 - 11:21 PM

[.net_Apprentice]

Thanks a lot for your help.
I am trying to fing out if I coud check if in the (local) server there is a Table.
It will be checked from a program by the user who don't know what a Data Base is.
That's why It can't be done mannually.
Thanks in advance.
Thanks

[andrewsw]

Here are two methods I would try:

cmd.CommandText = "SELECT COUNT(*) FROM sys.objects " & _
                           "WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') " & _
                           "AND type in (N'U')"

Try
    conn.Open()                             'Opens the connection
    cmd.Connection = conn                   'Instructs the cmd object to use conn as its connection when executing
    exists = CByte(cmd.ExecuteScalar())     'Use ExecuteScalar to return a single value, the count, and assign it to the exists variable
    conn.Close()
Catch ex As SqlException
    MessageBox.Show(ex.Message)             'Catch any SqlException and display it in a MessageBox
Catch ex As Exception
    MessageBox.Show(ex.Message)             'Catch any ApplicationException and display it in a MessageBox
End Try

not sure that the type check is necessary. Or,

SELECT *
FROM sys.tables
WHERE name = 'mytable'
AND schema_id = SCHEMA_ID('myschema')

(don't think the schema is necessary).

This post has been edited by andrewsw: 20 January 2013 - 04:09 PM

[.net_Apprentice]

Hi andrewsw
Thanks a lot for the help. I will go by your code.
Thanks again buddy