none
check table exists

    Question

  • hi

    in my ddl script i need to check if table exits in particular database , 

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

    this statement check in sql server, how to do in sql in particular database

    Friday, July 12, 2013 7:15 PM

Answers

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


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, July 12, 2013 7:21 PM

All replies

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


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, July 12, 2013 7:21 PM
  • Sebastian,

    this will not work if you are not in the contenxt of the database itself. I've cross checked it and it returns no record (as expected).

    These both solutions may work

    1. Solution: sp_executeSQL

    USE master;
    GO
    
    DECLARE	@object_id	int
    DECLARE	@parms	nvarchar(255)	=	N'@object_id int OUTPUT';
    DECLARE	@stmt	nvarchar(255)	=	N'USE mydb;
    SELECT @object_id = object_id FROM sys.objects WHERE object_id = OBJECT_ID(''dbo.tblstammdaten'', ''U'')'
    
    EXEC sp_executeSQL @stmt, @parms, @object_id = @object_id OUTPUT
    IF @object_id IS NULL
    	RAISERROR ('Relation does not exist!', 0, 1)
    ELSE
    	RAISERROR ('Relation exists', 0, 1)

     2. solution: Querying the system relations:

    SELECT @object_id = object_id
    FROM	mydb.sys.objects o INNER JOIN mydb.sys.schemas s
    		ON (o.schema_id = s.schema_id)
    WHERE	s.name = 'dbo' AND
    		o.name = 'tblStammdaten';
    SELECT	@object_id;

    :)

    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Friday, July 12, 2013 7:53 PM
  • Hi tsql_new,

    your script doesn't check in "SQL Server" but in the "current" database.

    Simpliest way:

    USE mydb;
    GO
    
    IF OBJECT_ID('dbo.yourtable', 'U') IS NOT NULL
       RAISERROR ('Table exists', 0, 1)
    ELSE
       RAISERROR ('Table does not exist', 0, 1)


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Friday, July 12, 2013 7:55 PM
  • version using 3-part object name and sys.tables:
    IF EXISTS (SELECT *
    	FROM MyDB.sys.tables t 
    	JOIN MyDB.sys.schemas s ON t.schema_id = s.schema_id
    	WHERE s.name = 'dbo' 
    	AND t.name = 'MyTableName'
    )
    PRINT 'Yes'
    ELSE
    PRINT 'No'


    Friday, July 12, 2013 8:10 PM