如何在 vb .net 项目中检查 sql server 中是否存在数据库和表？

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

提问by Coder92

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.

谁能告诉我如何从 vb.net 项目检查 sql server 中是否存在数据库和表？我想要做的是检查数据库是否存在（最好在“If”语句中，除非有人有更好的方法），如果它确实存在我做一件事，如果它不存在我创建数据库与表和列。对此事的任何帮助将不胜感激。

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.

应用程序连接到服务器。当应用程序在 PC 上运行时，我希望它检查数据库是否存在，如果存在，则执行它应该执行的操作，但是如果数据库不存在，则它首先创建数据库，然后继续执行它应该做什么。所以基本上我希望它在第一次在 PC 上运行时创建数据库然后开始它的业务，然后每次它在 PC 上运行之后我希望它看到数据库存在然后开始它的业务。我想要这样的原因是因为这个应用程序将在不止一台 PC 上，我只想要创建一次数据库和表，（第一次在 PC 上运行），然后当它在不同的 PC 上运行时，

回答by Tanner

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

您可以查询 SQL Server 以检查对象是否存在。

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:

下面的链接向您展示了如何使用 VB.NET 代码检查数据库是否存在于 SQL Server：

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

使用 vb.net 检查服务器上是否存在 SQL 数据库

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

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 EXISTScheck for both the database and a table:

您可以以另一种方式执行检查，因此它可以通过EXISTS对数据库和表使用检查在一次调用中完成：

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.

通过使用数据库和表名的参数调用上述代码一次，您将知道两者都存在。

回答by deterministicFail

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

我不知道确切的 vb 语法，但您只需检查结果的记录数

回答by PseudoToad

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.

对于 VB.NET，我会将它包装在一个存储过程中并调用它。我相信也有办法用 Linq 做到这一点。

回答by rohit patil

You can use This query for check database

您可以使用此查询来检查数据库

IF DB_Id('YourDatabaseName') IS NOT NULL

如果 DB_Id('YourDatabaseName') 不是 NULL

BEGIN

开始

PRINT 'DB EXISTS'

END

结尾

ELSE

别的

BEGIN

开始

PRINT 'DB  NOT EXISTS'

END

结尾

回答by Jose Lopez

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