Sql Query to list all views in an SQL Server 2005 database

List Views with sys.views

All the Views and More

If you want to see more than just the view names, go for:

SELECT * FROM sys.views;


Here, sys.views are akin to your own behind-the-scenes pass to view details that INFORMATION_SCHEMA.VIEWS hold back.

Bring in the Schemas

Like your coffee with a bit of cream and sugar? Here's the schema with your views:

SELECT s.name + '.' + v.name 
FROM sys.views v 
JOIN sys.schemas s ON s.schema_id = v.schema_id;


The schemas brings clear context to the views, makes the tricky ones more palatable.

Views with Indexed Properties

For all the views sporting cool indices:

SELECT * FROM sys.views
WHERE OBJECTPROPERTYEX(object_id, 'IsIndexed') = 1 
OR OBJECTPROPERTYEX(object_id, 'IsIndexable') = 1;


Yep, it's exactly like checking which of your friends are at the party or are definitely on the way!

Extracting Accurate View Definitions

Get the View Formula

To grasp the magic spell behind your view:

SELECT VIEW_DEFINITION 
FROM INFORMATION_SCHEMA.VIEWS 
WHERE TABLE_NAME = 'YourViewName';


Replace 'YourViewName' with the actual view name, of course, unless your database is whimsically named.

Filtering Views

Tailor your view list to find only your favorites:

SELECT * FROM sys.objects 
WHERE type = 'V' 
AND name LIKE 'YourCriteria%';


Remember to set 'YourCriteria%' to match your desired pattern. Just like setting filters on shopping websites to get your perfect fit.

Optimizing for Efficiency

Less is More

Remember, your queries should be like ninjas - fast, efficient, and unnoticeable. Keep in mind the impact on performance:

• Implement appropriate indexing on relevant system tables.
• Juggle your database context and workload nuances for these queries.

Don't Stray Far

Third-party tools may look tempting, but sys.views and other system views are your native SQL Server scouts. Stay compatible and secure.

Crucial Tips for Handling Views

Some Views Come Bundled

Avoid confusion between same-named views across schemas:

SELECT SCHEMA_NAME(schema_id) AS SchemaName, name AS ViewName 
FROM sys.views


Spot the Newcomers

Monitor your latest views, like checking for new Instagram followers:

SELECT name, create_date 
FROM sys.views
ORDER BY create_date DESC;


"It's Friday new-vi-ews, I'm in love" [Cue The Cure]

Mind the Gotchas

Dependencies are a Thing

Check for dependencies when modifying or dropping a view. Leaving them behind is like forgetting your umbrella in rain—it's going to hit you soon.

The Plan Matters

Check out the execution plan, especially for complex indexed views. It's like planning a road trip—every turn matters.

INFORMATION_SCHEMA Isn't all Truthful

Beware, the VIEW_DEFINITION in INFORMATION_SCHEMA can be truncated. For the full story, rely on the OBJECT_DEFINITION function. It's like needing director's cut for the true ending.