How to check if columns are being used by views?

Question

I have an excel sheet with list of columns mentioned in it. I would now like to know what all columns present in the excel sheet are being used by the views in sql(working in ssms). The data in excel is just the list of column names. It looks like.

ColumnNames

DimCurrencyId

Dimzoneid

.......

Nearly 1000 column names.

Now I need to find whether if these are being used in sql views or not?

i have nearly 500 views Can anyone please let me know the simplest way to do this so that i can skip the lot of manual work?

Answer 1

There is probably a much more elegant way to do this, but what I would do is use the Excel file to generate a list of SQL statements, then copy and paste them into SQL Server Management Studio and execute the entire list.

Assuming your column names begin in cell A2 I would put the following formula in cell B2 (or the next available column):

="select distinct sys.all_views.name from sys.all_views join sys.all_columns on sys.all_views.object_id = sys.all_columns.object_id where sys.all_columns.name = '"&A2&"' union"

OR, without needing a join (from ElenaDBA's answer):

="SELECT TABLE_NAME as [View Name] FROM   INFORMATION_SCHEMA.VIEWS WHERE  VIEW_DEFINITION LIKE '%"&A2&"%' UNION"

Drag the formula down to the end of your list of columns.

Copy and paste the resulting SQL statements into SSMS and be sure to remove the trailing union at the very end (the last query won't be unioning anything and you'll get an error if you try to execute the SQL with a trailing union statement).

Execute the SQL and you should get a list of the names of the views. Union automatically eliminates duplicates.

Again, not elegant, but I believe that will get the job done.

Update

To display the view name and the column name, you can simply add another reference to cells in column A to pick up your column as well. Additionally, we can modify the WHERE clause to only look at the dbo schema.

For the first formula that would change to (schema_id of dbo is 1):

="select distinct sys.all_views.name, '"&A2&"' as 'colName' from sys.all_views join sys.all_columns on sys.all_views.object_id = sys.all_columns.object_id where sys.all_columns.name = '"&A2&"'  and schema_id = 1 union"

Basically adding the value you are searching for in the where clause to the select part.

The second formula, using the same idea, would look like this (with the schema being referenced by name):

="SELECT TABLE_NAME as [View Name] , '"&A2&"' as 'colName' FROM   INFORMATION_SCHEMA.VIEWS WHERE  VIEW_DEFINITION LIKE '%"&A2&"%' AND TABLE_SCHEMA = 'dbo'  UNION"

Answer 2

try:

SELECT TABLE_NAME as [View Name]
FROM   INFORMATION_SCHEMA.VIEWS 
WHERE  VIEW_DEFINITION LIKE '%YourColumnName%'