如何使用MS SQL 2008获取数据库中的表列表？

I want to verify if a table exists in a database, and if it doesn't exist, to create it. How can I get a list of all the tables in the current database?

我想验证一个表是否存在于数据库中，如果它不存在，则创建它。如何获取当前数据库中所有表的列表？

I could get the database list with a SELECT like this:

我可以用这样的SELECT获取数据库列表：

SELECT * FROM sys.databases

What's left is to create the table if it doesn't exist.

剩下的就是创建表，如果它不存在。

I also tried to create the tables at the same time with the database like this:

我也尝试用这样的数据库同时创建表：

if not exists(select * from sys.databases where name = 'db')
begin 
    create database [db]
    use [db];
    create table [test] (
         Time datetime,
         Message varchar(1024) )
    end

But it gives me error on the 'use' line, saying that 'db' doesn't exist. This time, I will try to do this in 2 different commands.

但它在'使用'行上给我错误，说'db'不存在。这一次，我将尝试在两个不同的命令中执行此操作。

3 个解决方案

#1

This should give you a list of all the tables in your database

这应该为您提供数据库中所有表的列表

SELECT Distinct TABLE_NAME FROM information_schema.TABLES

So you can use it similar to your database check.

所以你可以使用它类似于你的数据库检查。

If NOT EXISTS(SELECT Distinct TABLE_NAME FROM information_schema.TABLES Where TABLE_NAME = 'Your_Table')
BEGIN
    --CREATE TABLE Your_Table
END
GO

#2

This query will get you all the tables in the database

此查询将获取数据库中的所有表

USE [DatabaseName];

SELECT * FROM information_schema.tables;

#3

Answering the question in your title, you can query sys.tables or sys.objects where type = 'U' to check for the existence of a table. You can also use OBJECT_ID('table_name', 'U'). If it returns a non-null value then the table exists:

回答标题中的问题，可以查询sys.tables或sys.objects，其中type ='U'来检查表是否存在。您还可以使用OBJECT_ID（'table_name'，'U'）。如果它返回非null值，则表存在：

IF (OBJECT_ID('dbo.My_Table', 'U') IS NULL)
BEGIN
    CREATE TABLE dbo.My_Table (...)
END

You can do the same for databases with DB_ID():

对于具有DB_ID（）的数据库，您可以执行相同的操作：

IF (DB_ID('My_Database') IS NULL)
BEGIN
    CREATE DATABASE My_Database
END

If you want to create the database and then start using it, that needs to be done in separate batches. I don't know the specifics of your case, but there shouldn't be many cases where this isn't possible. In a SQL script you can use GO statements. In an application it's easy enough to send across a new command after the database is created.

如果要创建数据库然后开始使用它，则需要在单独的批处理中完成。我不知道你的案件的细节，但不应该有很多情况下这是不可能的。在SQL脚本中，您可以使用GO语句。在应用程序中，在创建数据库之后发送新命令很容易。

The only place that you might have an issue is if you were trying to do this in a stored procedure and creating databases on the fly like that is usually a bad idea.

您可能遇到问题的唯一地方是，如果您尝试在存储过程中执行此操作并在运行中创建数据库，那通常是个坏主意。

If you really need to do this in one batch, you can get around the issue by using EXEC to get around the parsing error of the database not existing:

如果您确实需要在一个批处理中执行此操作，则可以通过使用EXEC解决不存在的数据库的解析错误来解决此问题：

CREATE DATABASE Test_DB2

IF (OBJECT_ID('Test_DB2.dbo.My_Table', 'U') IS NULL)
BEGIN
    EXEC('CREATE TABLE Test_DB2.dbo.My_Table (my_id INT)')
END

EDIT: As others have suggested, the INFORMATION_SCHEMA.TABLES system view is probably preferable since it is supposedly a standard going forward and possibly between RDBMSs.

编辑：正如其他人所建议的那样，INFORMATION_SCHEMA.TABLES系统视图可能更可取，因为它可能是一个标准，可能在RDBMS之间。

#1