Checking for a view or table

  • I'm looking for a way to check for a view or table and if it is there, do nothing, if it is not there create it. Typically there is the if then drop, this is not working for me:

    [typical]

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

    --typical drop view or drop table would go here. does anyone know a way to somehow do something like this

    [what I would like to do]

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

    Create view MyView etc . . .

  • Try this:

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[YOUR_VIEW]') and OBJECTPROPERTY(id, N'IsView') = 1)

    drop view [dbo].[Your_VIEW]

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Yout_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[Your_Table]

  • I'm looking for a way to check for a view or table and if it is there, do nothing, if it is not there create it. Typically there is the if then drop, this is not working for me:

    I think someone misread your intent....

    Try this...

    IF OBJECT_ID('table_name','U') IS NULL

    BEGIN

    ...put table creation code here...

    END

    IF OBJECT_ID('view_name','V') IS NULL

    BEGIN

    ...put view creation code here...

    END

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I think this is what I was looking for, I'll give it a try--thanks for the feedback!

  • Not a problem... let us know either way after you try it? Thanks...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • When I use the If OBJECT_ID() IS Null

    I get the same snytax errors as with the other. I'm not sure why it is doing it but t-sql does not seem to like the create statement right after the If. This is what I'm doing:

    IF OBJECT_ID('MyView','V') IS NULL

    BEGIN

    create view MyView as select * from MyTable

    END

    This generates a syntax errors just as the If Exists statement. Am I missing something?

  • When creating or altering views, the statement CREATE VIEW should be the first one in the batch.

    Its definitely not a good idea to create views in a procedure.

    --Ramesh


  • Try like this:

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

    BEGIN

    CREATE TABLE TABLENAME

    (

    COL1 INT,

    COL2 VARCHAR(10)

    )

    END

    GO

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

    BEGIN

    EXEC dbo.sp_executesql @statement = N'

    CREATE VIEW [dbo].[VIEWNAME]

    AS

    SELECT *

    FROM dbo.Test

    '

    END

    GO

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply