5 Replies - 3575 Views - Last Post: 20 January 2013 - 06:44 PM Rate Topic: -----

#1 .net_Apprentice  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 27-October 12

Check if DataBase Table already exists in SQL (local) Server

Posted 13 January 2013 - 10:18 PM

Hi guys:
Need to check fron VB.net if a Table exists in my SQL (local) Server.
Thanks in advance
Thanks
Is This A Good Question/Topic? 0
  • +

Replies To: Check if DataBase Table already exists in SQL (local) Server

#2 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1043
  • View blog
  • Posts: 4,057
  • Joined: 02-July 08

Re: Check if DataBase Table already exists in SQL (local) Server

Posted 13 January 2013 - 10:54 PM

Why do you need this from code? Do you have SSMS to open the DB and edit/create/drop tables on your server instance?
Was This Post Helpful? 0
  • +
  • -

#3 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

Re: Check if DataBase Table already exists in SQL (local) Server

Posted 13 January 2013 - 11:17 PM

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

Was This Post Helpful? 1
  • +
  • -

#4 .net_Apprentice  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 27-October 12

Re: Check if DataBase Table already exists in SQL (local) Server

Posted 20 January 2013 - 03:41 PM

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
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3227
  • View blog
  • Posts: 10,840
  • Joined: 12-December 12

Re: Check if DataBase Table already exists in SQL (local) Server

Posted 20 January 2013 - 04:09 PM

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

Was This Post Helpful? 1
  • +
  • -

#6 .net_Apprentice  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 27-October 12

Re: Check if DataBase Table already exists in SQL (local) Server

Posted 20 January 2013 - 06:44 PM

Hi andrewsw
Thanks a lot for the help. I will go by your code.
Thanks again buddy
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1