Finding SQL Server views with (or without) a certain property

Problem

In my last tip, I demonstrated SQL Server metadata queries to help you find all the tables in your SQL Server database that meet some criteria. You might also be interested in similar queries to discover SQL Server views that have certain properties or use certain syntax.

Solution

Again, I have many SQL Server scripts lying around that I use quite often for exactly this purpose – sometimes I want to know which SQL Server views in a database are indexed, and sometimes I want to know how many of my views are dependent upon other views.

And again these are mostly just discovery scripts, meant only to list the views that meet the given criteria – you will need to dig further to get more details about the objects and, more importantly, to fix any that you deem to be problems.

All SQL Server views with SCHEMABINDING

SELECT [view] = s.name + N'.' + v.name 
  FROM sys.views AS v
  INNER JOIN sys.schemas AS s
  ON v.[schema_id] = s.[schema_id]
  WHERE EXISTS
  (
    SELECT 1 FROM sys.sql_modules AS m
      WHERE m.[object_id] = v.[object_id]
      AND m.is_schema_bound = 1
  );

All SQL Server views that are indexed

SELECT [view] = s.name + N'.' + v.name 
  FROM sys.views AS v
  INNER JOIN sys.schemas AS s
  ON v.[schema_id] = s.[schema_id]
  WHERE EXISTS
  (
    SELECT 1 FROM sys.indexes AS i
      WHERE i.[object_id] = v.[object_id]
      AND i.index_id = 1
  );

All SQL Server views that use SELECT *

SELECT [view] = s.name + N'.' + v.name 
  FROM sys.views AS v
  INNER JOIN sys.schemas AS s
  ON v.[schema_id] = s.[schema_id]
  WHERE EXISTS
  (
    SELECT 1 FROM sys.views AS sv
    INNER JOIN sys.schemas AS ss
    ON sv.[schema_id] = ss.[schema_id] 
    CROSS APPLY sys.dm_sql_referenced_entities
      (ss.name + N'.' + sv.name, N'OBJECT') AS r
    WHERE sv.[object_id] = v.[object_id]
      AND r.referenced_minor_id = 0
      AND r.is_select_all = 1
  );

All SQL Server views that reference other views

SELECT [view] = s.name + N'.' + v.name 
  FROM sys.views AS v
  INNER JOIN sys.schemas AS s
  ON v.[schema_id] = s.[schema_id]
  WHERE EXISTS
  (
    SELECT 1 FROM sys.views AS sv
    INNER JOIN sys.schemas AS ss
    ON sv.[schema_id] = ss.[schema_id] 
    CROSS APPLY sys.dm_sql_referenced_entities
      (ss.name + N'.' + sv.name, N'OBJECT') AS r
    INNER JOIN sys.views AS rv
    ON r.referenced_id = rv.[object_id]
    WHERE sv.[object_id] = v.[object_id]
  );

All SQL Server views that are referenced by other views

SELECT [view] = s.name + N'.' + v.name 
  FROM sys.views AS v
  INNER JOIN sys.schemas AS s
  ON v.[schema_id] = s.[schema_id]
  WHERE EXISTS
  (
    SELECT 1 FROM sys.views AS sv
    INNER JOIN sys.schemas AS ss
    ON sv.[schema_id] = ss.[schema_id] 
    CROSS APPLY sys.dm_sql_referencing_entities
      (ss.name + N'.' + sv.name, N'OBJECT') AS r
    INNER JOIN sys.views AS rv
    ON r.referencing_id = rv.[object_id]
    WHERE sv.[object_id] = v.[object_id]
  );

All SQL Server views with NOLOCK or READUNCOMMITTED hints

Note that any query that relies on brute force parsing of the object’s definition (since the metadata does not explicitly note things like table hint usage) is potentially subject to false positives. This will return all views using either of these table hints, but could returns views with the hints commented out, or with those words being used as part or all of an alias or other entity name (consider a table named dbo.Infer<b>noLock</b>s).

SELECT [view] = s.name + N'.' + v.name 
  FROM sys.views AS v
  INNER JOIN sys.schemas AS s
  ON v.[schema_id] = s.[schema_id]
  WHERE EXISTS
  (
    SELECT 1 FROM sys.sql_modules AS m
      WHERE m.[object_id] = v.[object_id]
      AND (LOWER(m.[definition]) LIKE N'%nolock%'
       OR LOWER(m.[definition]) LIKE N'%readuncommitted%')
  );

All SQL Server views that use TOP (100) PERCENT

TOP (100) PERCENT in a view is meaningless, and is usually used merely to support an ORDER BY clause – which is also meaningless in a view. If you use this construct, it will get optimized away and not obeyed anyway, so why give the impression that a SELECT from the view without an ORDER BY might guarantee that the results will be returned in that order, when that guarantee can’t possibly be made? I also add a check for TOP 99.99 PERCENT and TOP 2147483647, since these are common “workarounds.”

Note that this is another one that relies on parsing the entire definition of the view, and therefore, it could produce false positives.

SELECT [view] = s.name + N'.' + v.name 
  FROM sys.views AS v
  INNER JOIN sys.schemas AS s
  ON v.[schema_id] = s.[schema_id]
  WHERE EXISTS
  (
    SELECT 1 FROM sys.sql_modules AS m
      WHERE m.[object_id] = v.[object_id]
      AND (LOWER(m.[definition]) LIKE N'%top%100%percent%'
      OR LOWER(m.[definition]) LIKE N'%top%99.99%percent%'
      OR LOWER(m.[definition]) LIKE N'%top%2147483647%')
  );

Conclusion

I hope that has provided a good variety of metadata queries to help you identify views that meet some criteria. I certainly didn’t cover every potential scenario, but tried to touch enough areas to give you a head start on any areas I didn’t hit directly.

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *