如何在数据库中获取表列表,使用MS SQL 2008? [英] How to get table list in database, using MS SQL 2008?
问题描述
我要验证数据库中是否存在表,如果不存在,则创建它。
如何获取当前数据库中所有表的列表?
我可以使用SELECT来获取数据库列表,如下所示:
SELECT * FROM sys.databases
剩下的就是创建表,如果它不存在。
我也试图创建表与数据库同时这样:
如果不存在(select * from sys.databases where name ='db')
begin
create database [db]
use [db];
create table [test](
时间datetime,
消息varchar(1024))
end
但它给了我在'使用'行的错误,说'db'不存在。这一次,我将尝试在两个不同的命令这样做。
解决方案这将给你一个列表的所有表您的数据库
SELECT Distinct TABLE_NAME FROM information_schema.TABLES
所以你可以使用它类似于你的数据库检查。
如果NOT EXISTS (SELECT Distinct TABLE_NAME FROM information_schema.TABLES Where TABLE_NAME ='Your_Table')
BEGIN
--CREATE TABLE Your_Table
END
GO
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 * 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.
解决方案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
这篇关于如何在数据库中获取表列表,使用MS SQL 2008?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!