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

sql sql-server tsql sql-server-2008-r2
Prady picture Prady · May 10, 2011 · Viewed 394.6k times · Source

I am writing a Stored procedure in SQL Server 2008. I need to check if a table exists in the database. If it doesn't then I need to create it.

How do I do this?

Answer

SQLMenace picture SQLMenace · May 10, 2011

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

Related questions

Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build). Sometimes when I run my build I get this error: ALTER DATABASE failed because a lock could not be placed on …

Read More
SQL Server 2008: How to query all databases sizes?

I have MS SQL 2008 R2, 500 databases. What is the most efficient, easiest and 'modern' way to query all databases sizes. The output should have columns: DatabaseName DataFilesSize LogFilesSize

Read More
Using IF ELSE statement based on Count to execute different Insert statements

While I am searching through my database, I run an INSERT statement if I find that a particular item does not exist, and I run a different INSERT statement if I find one or more of this item. I am …

Read More