How to check if a database and tables exist in sql server in a vb .net project?

Question

0.00/5 (No votes)

See more:

Can anyone tell me how I would go about checking if a database and tables exists in sql server from a vb.net project? What I want to do is check if a database exists (preferably in an 'If' statement, unless someone has a better way of doing it) and if it does exist I do one thing and if it doesn't exist I create the database with the tables and columns. Any help on this matter would be greatly appreciated.

Edit:

The application has a connection to a server. When the application runs on a PC I want it to check that a database exists, if one exists then it goes and does what it's supposed to do, but if a database does NOT exist then it creates the database first and then goes on to do what it's supposed to do. So basically I want it to create the database the first time it runs on a PC and then go about it's business, then each time it runs on the PC after that I want it to see that the database exists and then go about it's business. The reason I want it like this is because this application will be on more than one PC, I only want the database and tables created once, (the first time it runs on a PC) and then when it runs on a different PC, it sees that the database already exists and then run the application using the existing database created on the other PC.

Posted 6-Aug-14 4:13am

Member 10804519

Updated 6-Aug-14 5:23am

v2

Add a Solution

3 solutions

Solution 1

Check this codeproject article

Check if a table or field exists in a database[^]

and this post here

Posted 6-Aug-14 4:21am

Dilan Shaminda

Solution 2

Try to open the connection to the Server. If it causes an exception you know.

VB

Using oSqlConnection As New SqlConnection("ConnectionString")
        oSqlConnection.Open()
End Using
)

To check for a table.

SQL

SELECT *
FROM INFORMATION_SCHEMA.TABLES T
WHERE T.TABLE_NAME LIKE '%MY_TABLE%'

Putting it all together in VB.

VB

Using oSqlConnection As New SqlConnection(My.Settings.ProductTraxConnectionString)
	oSqlConnection.Open()
	Using oQuery As New SqlCommand(
		"SELECT COUNT(*) " &
		"FROM INFORMATION_SCHEMA.TABLES T " & 
		"WHERE T.TABLE_NAME LIKE @LookupTable", oSqlConnection)
		oQuery.Parameters.AddWithValue("@LookupTable", "%MY_TABLE%")


		Dim oResult as object = oQuery.ExecuteScalar()
	End Using
End Using

Posted 6-Aug-14 4:28am

SteveyJDay

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Neetin_1809 · Accepted Answer · 2014-08-06T09:02:00

Solution 3

If you are using sql server the following query

select * from sys.Databases where Name='urDataBaseName'

Gets you the database details

For Checking the Table in that DataBase
Use urDataBaseName;
select * from TableName

Let Me Know this was the Think u was the think you asking.
Happy Coding :-)

Posted 6-Aug-14 9:02am

Neetin_1809

Comments

Member 10804519 7-Aug-14 4:33am

Thank you, this is part of what I am looking for. I think I have found how to do the rest of it on the following link:
http://kellyschronicles.wordpress.com/2009/02/16/check-if-sql-database-exists-on-a-server-with-vb-net/