How to Check for and Create Tables in SQL Server 2008?

Verifying and Creating Tables in SQL Server 2008

In SQL Server, ensuring the existence of a table is crucial before performing operations. This article addresses how to check if a table exists in SQL Server 2008 and, if it doesn't, create it.

Checking for Table Existence

To check for the existence of a table, use the following syntax:

IF NOT EXISTS (
    SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[YourTable]') AND type in (N'U')
)
BEGIN
    -- Table does not exist, create it

Creating the Table

If the table does not exist, the following block will be executed to create it:

    CREATE TABLE [dbo].[YourTable](
        -- Define your table schema here
    ) 
END

Example Usage

Consider the following stored procedure:

CREATE PROCEDURE CheckAndCreateTable
AS
BEGIN
    IF NOT EXISTS (
        SELECT *
        FROM sys.objects
        WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U')
    )
    BEGIN
        CREATE TABLE [dbo].[MyTable](
            ID INT IDENTITY(1,1) NOT NULL,
            Name VARCHAR(50) NOT NULL
        ) 
    END
END

This procedure checks if the table MyTable exists in the dbo schema. If not, it creates the table with an ID column as the primary key and a Name column as a non-null field.

The above is the detailed content of How to Check for and Create Tables in SQL Server 2008?. For more information, please follow other related articles on the PHP Chinese website!