CODEWITHSUNDEEP
sqlsql-servervb.netsql-server-2008
Coder92
Coder92

Reputation: 141

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

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.

Upvotes: 3

Views: 44372

Answers (6)

Reagan
Reagan

Reputation: 79

   Public Function SQLDatabaseExist(ByVal DefaultConnectionString As String, ByVal DataBaseName As String) As Boolean
    Try
        'CREATE DATABASE
        Dim SqlString As String = ""
        SqlString = "SELECT CASE WHEN EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'" & DataBaseName & "') THEN CAST (1 AS BIT) ELSE CAST (0 AS BIT) END"
        Dim ExcRet As Integer = 0
        Using connection As New SqlConnection(DefaultConnectionString)
            Dim command As New SqlCommand(SqlString, connection)
            command.Connection.Open()
            ExcRet = command.ExecuteScalar()
            command.Connection.Close()
            command.Dispose()
        End Using
        Return ExcRet
    Catch ex As Exception
        Return False
    End Try
End Function

Upvotes: 0

Jose Lopez
Jose Lopez

Reputation: 11

Friend Function CheckDatabaseExists(server As String, database As String) As Boolean
    Dim connString As String = "Data Source=" + server + ";Initial Catalog=master;Integrated Security=SSPI"

    Dim cmdText As String = "select * from master.dbo.sysdatabases where name='" + database + "'"

    Dim bRet As Boolean = False

    Using sqlConnection As SqlConnection = New SqlConnection(connString)
        sqlConnection.Open
        Using sqlCmd As SqlCommand = New SqlCommand(cmdText, sqlConnection)
            Using reader As SqlDataReader = sqlCmd.ExecuteReader
                bRet = reader.HasRows
            End Using
        End Using
    End Using

    Return bRet

End Function

Upvotes: 1

Tanner
Tanner

Reputation: 22733

You can query SQL Server to check for the existence of objects.

To check for database existence you can use this query:

SELECT * FROM master.dbo.sysdatabases WHERE name = 'YourDatabase'

To check for table existence you can use this query against your target database:

SELECT * FROM sys.tables WHERE name = 'YourTable' AND type = 'U'

This below link shows you how to check for database existence is SQL Server using VB.NET code:

Check if SQL Database Exists on a Server with vb.net

Referenced code from above link:

Public Shared Function CheckDatabaseExists(ByVal server As String, _
                                           ByVal database As String) As Boolean
    Dim connString As String = ("Data Source=" _
                + (server + ";Initial Catalog=master;Integrated Security=True;"))

    Dim cmdText As String = _
       ("select * from master.dbo.sysdatabases where name=\’" + (database + "\’"))

    Dim bRet As Boolean = false

    Using sqlConnection As SqlConnection = New SqlConnection(connString)
        sqlConnection.Open
        Using sqlCmd As SqlCommand = New SqlCommand(cmdText, sqlConnection)
            Using reader As SqlDataReader = sqlCmd.ExecuteReader
                bRet = reader.HasRows
            End Using
        End Using
    End Using

    Return bRet

End Function

You could perform the check in another way, so it's done in a single call by using an EXISTS check for both the database and a table:

IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE name = 'YourDatabase')
BEGIN
    -- Database creation SQL goes here and is only called if it doesn't exist
END

-- You know at this point the database exists, so check if table exists

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'YourTable' AND type = 'U')
BEGIN
    -- Table creation SQL goes here and is only called if it doesn't exist
END

By calling the above code once with parameters for database and table name, you will know that both exist.

Upvotes: 6

rohit patil
rohit patil

Reputation: 111

You can use This query for check database

IF DB_Id('YourDatabaseName') IS NOT NULL

BEGIN

PRINT 'DB EXISTS'

END

ELSE

BEGIN

PRINT 'DB  NOT EXISTS'

END

Upvotes: 2

PseudoToad
PseudoToad

Reputation: 1574

For tables and other objects inside a database, I usually do it this way but it's really personal preference.

IF OBJECT_ID('dbo.blah') IS NOT NULL
BEGIN

END

For VB.NET, I'd wrap this in a stored procedure and call that. I'm sure there are also ways to do this with Linq.

Upvotes: 3

deterministicFail
deterministicFail

Reputation: 1285

Connect to the master database and select

SELECT 1 FROM master..sysdatabases WHERE name = 'yourDB'

and then on the database

SELECT 1 FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_NAME = 'yourTable'

i dont know the exact vb syntax but you only have to check the recordcount on the result

Upvotes: 3

Related Questions