How do you check if a view exists before you have it created?

Asked

Viewed 2,862 times

0

Take as an example the following view:

CREATE VIEW vw_types AS
SELECT
    codigo AS code,
    nome AS description,
    abreviacao AS abbreviation,
    statusRegistro AS status
FROM 
    tipos

How can I have it created only if it does not exist in the database?

sql sql-server tsql

  • I believe the best approach would be here: http://stackoverflow.com/a/639127/1739930. since CREATE VIEW should be the only instruction of a lot it is difficult to place it inside a IF OBJECT_ID('vw_types ') IS NULL

1 answer

1


If you don’t want to change or delete the View that already exists, just check if it exists, if it exists perform some action or do nothing.

      IF NOT EXISTS (
        SELECT * FROM sys.views v
        INNER JOIN sys.schemas s on v.schema_id = s.schema_id
        WHERE s.name = 'dbo' and v.name = 'vw_types'
    )
        EXEC sp_executesql @statement = N'CREATE VIEW vw_types AS
SELECT
    codigo AS code,
    nome AS description,
    abreviacao AS abbreviation,
    statusRegistro AS status
FROM 
    tipos;' ;
ELSE
        print 'ja exixte uma view com esse nome'
GO

In this case I’m just bringing a message informing that it already exists on ELSE print 'ja exixte uma view com esse nome', but this is optional. You can delete, or have another action.

Browser other questions tagged sql sql-server tsql

You are not signed in. Login or sign up in order to post.