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

By:   |   Comments (6)   |   Related: More > Database Administration


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.InfernoLocks). 

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, December 4, 2014 - 1:45:05 PM - Pete Back To Top (35512)

It's never as easy as sounds at first. Oh well, it was worth a try. Some of those issues don't apply to my situation, but enough do that it's probably more trouble than it's worth to pursue it.

I suppose the best idea is to do as you say - bail on schemabinding until a DB is fairly stable. And when I do have to change an older, more established one, I can still use your script to find which ones I need to unbind, then use the SSMS Generate Scripts Wizard to generate all the necessary ALTER scripts at once.

Some of my creations use quite a few views, with schemas for permission management. I tend to use views to address specific needs, with exactly those fields needed for a particular result set, rather than opening tables directly and selecting fields that I want. Since I am a one-man show, doing DB dev, app dev, UI design, server admin, backup - in short, eveything, I can be fairly cosmopolitan about how I design things. Tables out of sight, specifically tailored views for reading/searching, and stored procedures for updating/adding seem to give me the best balance of results in terms of security, performance and ease of use.

In any case, thanks for taking the time to address my questions. Always a pleasure to learn something new.

Wednesday, December 3, 2014 - 1:14:43 PM - Aaron Bertrand Back To Top (35488)

I also forgot to mention that the column headers can be defined with the object name (just like a CTE), so you could have a view defined as follows:

 

CREATE VIEW dbo.view(a,b,c)

AS

  SELECT 1,2,3;

 

And even more fun:

 

CREATE VIEW dbo.view(a,b,c)

AS

  WITH x(m,n,o) AS (SELECT p = 1, q = 2, r = 3) SELECT s = m, t = n, u = o FROM x;

 

Parsing and rebuilding that view will be fun as well... not really *different* than the first, just illustrating that it may not be too clear to a human or to your code what the output columns should really be.

 

Wednesday, December 3, 2014 - 12:29:01 PM - Aaron Bertrand Back To Top (35485)

Pete, that would be easy enough to do. For example if you want a single script containing all the views that currently have schemabinding (so that you can remove it), you can set SSMS to results to text and say:

 
SELECT N'GO' + CHAR(13) + CHAR(10) + COALESCE(m.definition, N'') 
  FROM sys.views AS v
  INNER JOIN sys.schemas AS s
  ON v.[schema_id] = s.[schema_id]
  INNER JOIN sys.sql_modules AS m
      ON m.[object_id] = v.[object_id]
      WHERE m.is_schema_bound = 1;
 

However, there are a ton of caveats:

  1. The COALESCE is there in case you have encrypted views (you could add a where clause against OBJECTPROPERTY or sys.syscomments to filter those out, since you won't be able to alter those directly unless you have the definitions in source control).
  2. No view can exceed the "Maximum number of characters displayed in each column" limit in Tools > Query Results > SQL Server > Results to Text (default 256; max 8192), or else the text will be truncated. If you have views that are larger than 8k, you could store the results in a table or concatenate them into a single variable, but you'd still need to find a way to output them to a query window so that you could do search and replace. Or really trust your parsing abilities to write the T-SQL that can make the adjustments to the dynamic SQL before executing it (see below for a few parsing difficulties). An application is looking more attractive with each bullet point, no?
  3. You need to add SET statements to maintain the proper ANSI_NULLS / QUOTED_IDENTIFIER settings for each view, which may be different from your current settings in SSMS (these can be derived from sys.sql_modules). You don't want a view to suddenly start breaking because one of these settings changed inadvertently.
  4. If you remove SCHEMABINDING from an indexed view, this will also drop all the indexes. You will first need to generate the scripts for these as well so that you can re-create them when you add SCHEMABINDING back to the views.
  5. You will need to perform a find/replace to change CREATE to ALTER (I don't know exactly how SSMS does this when you script > as alter; it is done within the application code somewhere and not in the T-SQL that a trace captures) or just perform drop / re-create. The latter can cause dependency problems depending on the output or execution order of your eventual script, and also requires that you script any explicit permissions before the drop, too.
There are probably other ways you can do this, but they would involve manual parsing, and I can assure you that parsing view definitions to reconstruct them from their parts is *hard*:
  1. Schema may or may not be present in the original view, and you may not want to add it to the definition when you build a new script.
  2. Obvious breakers like AS, SELECT, WITH, and the view schema and name may appear earlier or later than you expect them (for example, any of those can appear in comments before the CREATE, a view could be named AS or SELECT or contained within a schema with either of those names, and the SELECT may not appear until *after* a CTE).
It would be fantastic to be able to parse the query out of the view, which would allow you to set the header any way you want and then just add the query. But SQL Server currently doesn't store these separately, and all of the above complications make it really hard to do yourself, too. I don't think full automation is possible - identification of all the views that reference a certain table that you want to change *and* are schema-bound to that table is not much of a stretch beyond the queries I've provided above; then you can generate those scripts and manually make the changes. If this is more tedious than trying to overcome all of the above limitations, then I might question (a) why you have so many views or (b) why you are doing this so often. If you are in a heavy development cycle where underlying tables are changing this much, take schemabinding off the views and leave it off until the dev cycle is over.

Wednesday, December 3, 2014 - 4:22:48 AM - Pete Back To Top (35472)

Thanks - I rather suspected it would not be that simple. However, it's still a regular issue for me. In particular, when I'm working on an existing database, and need to change some of the structure, I often crash into schema-bound views. It's a pain in the fundament to try saving a table, get an error message, locate the offending view, generate an ALTER script, remove the schemabinding clause, run it, try to save the table again... over and over, until all such views have been unbound, finally save the table, then reverse the process for every view that I had to change. These scripts of yours at least allow such views to be located quickly, but unbinding and re-binding them all is still a nuisance. Do you have any thoughts on this? Give up on schemabinding? Just deal with it?

One thing occurs to me, that might be a workable compromise - modify these scripts to not only find views, but automatically generate ALTER scripts for EVERY view found, into one window. That way, a simple find / replace could be used to clear out all schemabinding clauses, run the modified ALTERs all together to modify all views at once, save the modified table, then a simple Ctrl/Z to return the schemabinding clauses, and run the compound ALTERs again to return the views to their bound state.

It would not be completely automatic, but it might automate enough of the drudge-work to reduce the hassle.

Tuesday, December 2, 2014 - 11:13:01 AM - Aaron Bertrand Back To Top (35464)

Hi Pete, that would be a little more involved - you'd need to generate the ALTER VIEW statements in dynamic SQL, which can be cumbersome, then you'd also need to either have logic to determine whether a view can actually be schemabound (check for left joins, select *, system objects, etc. etc. ad nauseum - the list of disqualifiers is non-trivial) or wrap each ALTER in TRY/CATCH. Definitely beyond the scope of this tip, but I can take a look at it for a future post.

Tuesday, December 2, 2014 - 8:40:34 AM - Pete Back To Top (35463)

Pretty neat. I regularly look for views that have or don't have SchemaBinding. How hard would it be to modify this to make it add or remove something, like add SchemaBinding to every view that doesn't have it?














get free sql tips
agree to terms