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

Question

I want to write code to check table already exist or not in SQL Server 2008 and If Not Exists then create it and then insert records into it. Please tell me how to do it? Is it necessary to create Stored Procedure for it?

score 5 · Accepted Answer · answered Aug 23 '13 at 09:54

5

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

answered Aug 23 '13 at 09:54

Giannis Paraskevopoulos

18,261
1
49
69

score 0 · Answer 2 · answered Aug 23 '13 at 09:55

0

(SELECT count(*) 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable')

if count return 0 it means the table is not exits

answered Aug 23 '13 at 09:55

chinna_82

6,353
17
79
134

score 0 · Answer 3 · answered Aug 23 '13 at 10:01

You need to look into a system view to do this:

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SomeTable]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[SomeTable](
        [SomeId] [int] NOT NULL
    )
END ELSE PRINT 'SomeTable already exists.';

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

3 Answers3