Check If Table Exists and If It Doesn't Exist, Create It in SQL Server 2008

Check if table exists and if it doesn't exist, create it in SQL Server 2008

Something like this

IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[YourTable]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[YourTable](
    ....
    ....
    ....
) 

END

Check if table exists in SQL Server

For queries like this it is always best to use an INFORMATION_SCHEMA view. These views are (mostly) standard across many different databases and rarely change from version to version.

To check if a table exists use:

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff
END

How to Check table Exist or Not and then Create a table if not Exist?

IF NOT EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES T
        WHERE T.TABLE_SCHEMA = 'dbo'
        AND T.TABLE_NAME = 'YOURTABLENAME'  )
    BEGIN

        CREATE TABLE dbo.YOURTABLENAME
        (
            ColumnDefinitionsHere
        )
    END

GO

T-SQL Can't test if table exists

It's because when the object db.schema.sqlt_table_1 doesn't exist the statement fails. It doesn't matter if the statement won't run, the batch must still be valid.

The statement @VAR = (SELECT 1.0 FROM db.schema.sqlt_table_1); isn't even valid though. You would be better off with this:

USE db;
GO
SELECT CASE WHEN EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'sqlt_table_1' AND SCHEMA_NAME = N'schema') THEN 1 END;

Table exists but doesn't appear SQL Server

That's a #temporary table. It's created in tempdb, not your user database. You're not going to find it in Object Explorer, and it's only visible in the query window that created it. It will disappear when that session ends.

How to drop a table if it exists?

Is it correct to do the following?

IF EXISTS(SELECT *
          FROM   dbo.Scores)
  DROP TABLE dbo.Scores

No. That will drop the table only if it contains any rows (and will raise an error if the table does not exist).

Instead, for a permanent table you can use

IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL 
  DROP TABLE dbo.Scores; 

Or, for a temporary table you can use

IF OBJECT_ID('tempdb.dbo.#TempTableName', 'U') IS NOT NULL
  DROP TABLE #TempTableName; 

SQL Server 2016+ has a better way, using DROP TABLE IF EXISTS …. See the answer by @Jovan.

Add a column to a table, if it does not already exist

You can use a similar construct by using the sys.columns table io sys.objects.

IF NOT EXISTS (
  SELECT * 
  FROM   sys.columns 
  WHERE  object_id = OBJECT_ID(N'[dbo].[Person]') 
         AND name = 'ColumnName'
)

How to check if a column exists in a SQL Server table

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

---

Related Topics