CODEWITHSUNDEEP
sqlsql-serverview
saul
saul

Reputation: 979

How do I get all the SQL Server views names that have "SELECT * FROM..."

In order to identify high risk views when making changes to underlying tables and be able to do sp_refreshview, I'd like to get the names of all views that are querying all (*) the columns from a table using the SELECT *

For example, find views with cases like:

SELECT *
SELECT SomeAlias.*

Note: I understand that SELECT * is a bad practice and strongly not recommended

Upvotes: 1

Views: 68

Answers (1)

saul
saul

Reputation: 979

This is how I did it:

SELECT DISTINCT
       o.name AS ObjectName,
       o.type_desc ObjectType,
       SUBSTRING (m.definition, PATINDEX ( '%[.][*]%' , m.definition )-4, 20) MatchedPattern
  FROM sys.sql_modules m
       INNER JOIN
       sys.objects o
         ON m.object_id = o.object_id
 WHERE  m.definition Like '%[.][*]%'
 AND o.type_desc  IN ('VIEW')

Upvotes: 1

Related Questions