How can I check if a View exists in a Database?

Detailed guide (with bonus 😁 jokes)

When dealing with database views, verifying their existence before operating on them is critical. Let's explore in detail how we can handle this using Microsoft SQL Server's catalog views, ANSI-standard views, and system functions.

The sys.views Catalog View

In SQL Server, sys.views stores information about views in your database. Here's how to find a specific view:

-- Even among hundreds of views, there's the one!
IF EXISTS (SELECT * FROM sys.views WHERE name = N'YourView' AND schema_id = SCHEMA_ID('YourSchema'))
    PRINT 'View was found. This calls for a victory dance!';
ELSE
    PRINT 'View MIA. Time for search part 2...double-check the schema maybe?';


The INFORMATION_SCHEMA.VIEWS List

It provides cross-RDBMS compatibility due to its ANSI-standard specification. A lifesaver when dealing with different SQL-compliant databases:

-- Looking for a needle(view) in a haystack(database)!
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'YourSchema' AND TABLE_NAME = 'YourView')
    PRINT 'Found it. Phew!';
ELSE
    PRINT 'No luck, view not found. Have you misplaced it?';


The OBJECT_ID Magic Function

No, it's not an illusion! OBJECT_ID checks for object existence, applicable to views, tables, and even stored procedures:

-- Is your view shy? Let's check!
IF OBJECT_ID(N'YourSchema.YourView', 'V') IS NOT NULL
    PRINT 'There you are, sneaky view!';
ELSE
    PRINT 'POOF! No view in sight.';


Honing your DROP skills

SQL Server 2016 brought this gem—DROP VIEW IF EXISTS—letting you bid farewell to views with elegance:

-- Saying goodbye to our view...if it even exists!
DROP VIEW IF EXISTS YourSchema.YourView;


Counting INFORMATION_SCHEMA.VIEWS Entries

When IF EXISTS is overpowered, count the rows instead:

-- Counting views...hmm, are we out of sheep to count?
SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'YourSchema' AND TABLE_NAME = 'YourView';


This will display 0 if your view took a vacation or a positive integer if it's ready for work.

Nailing the best practices and avoiding “oops!” moments

Writing optimised, reliable, and maintainable code is a must-have skill. Here's a best practice sandwich, stuffed with tasty guidelines and sprinkled with a pinch of edge-case consideration.

The importance of schema qualifiers

Always include schema qualifiers when referring to views—it serves as the street name in an address—or risk ambiguity and much avoidable confusion.

Wildcards or straight shot: SELECT 1

When checking for existence, SELECT 1 is preferable over SELECT *. The latter feels like fetching water with a fire hose when a cup would suffice.

The performance cost: Isn't it just a check?

Checks might seem innocent, but they can affect performance, especially within large databases or frequent validations. Optimize your queries to make your database happy and healthy.

The superpower of INFORMATION_SCHEMA.VIEWS: Cross-RDBMS compatibility

INFORMATION_SCHEMA.VIEWS works in SQL Server, PostgreSQL, MySQL etc. Superman's got nothing on this cross-RDBMS compatibility!

Keep your scripts resilient

Always perform existence checks prior to actions on views. Good script hygiene prevents errors and ensures self-documentation, enhancing readability and maintenance.

Future-proofing your checks

Be careful with version-specific features like DROP VIEW IF EXISTS—your code should play nice with all the versions your databases might be running on.