How to get table list in database, using MS SQL 2008?

Question

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.

Answer 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 

Answer 2

This query will get you all the tables in the database

USE [DatabaseName];  SELECT * FROM information_schema.tables;