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

User · Accepted Answer

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

User · Answer

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   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     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   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   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

User · Answer

This query will get you all the tables in the database  USE  DatabaseName    SELECT   FROM information schema tables

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

Examples related to sql

Examples related to sql-server-2008

Examples related to select