如何使用 MS SQL 2008 获取数据库中的表列表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6598289/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get table list in database, using MS SQL 2008?
提问by Tibi
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”不存在。这一次，我将尝试在 2 个不同的命令中执行此操作。
回答by Brandon Boone
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
回答by John Hartsock
This query will get you all the tables in the database
此查询将为您获取数据库中的所有表
USE [DatabaseName];
SELECT * FROM information_schema.tables;
回答by Tom H
Answering the question in your title, you can query sys.tablesor sys.objectswhere 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')。如果它返回一个非空值，则该表存在：
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 GOstatements. 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.TABLESsystem view is probably preferable since it is supposedly a standard going forward and possibly between RDBMSs.
编辑：正如其他人所建议的那样，INFORMATION_SCHEMA.TABLES系统视图可能更可取，因为它被认为是未来的标准，并且可能在 RDBMS 之间。
