如何检查视图是否正在使用列? [英] How to check if columns are being used by views?

问题描述

我有一个Excel工作表，上面提到了列的列表.我现在想知道sql中的视图正在使用excel工作表中存在的所有列(在ssms中工作).excel中的数据只是列名称的列表.看起来像.

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

DimCurrencyId

Dimzoneid

Dimzoneid

.......

近1000个列名.

现在我需要查找这些是否在sql视图中使用?

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

我有近500次观看有人可以让我知道最简单的方法吗，这样我就可以跳过很多手动工作?

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?

推荐答案

执行此操作的方法可能更优雅，但是我要做的是使用Excel文件生成SQL语句列表，然后复制并将它们粘贴到SQL Server Management Studio中并执行整个列表.

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.

假设您的列名以单元格 A2 开头，则将以下公式放入单元格 B2 (或下一个可用的列)中:

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"

或者，不需要加入(来自 ElenaDBA的答案):

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.

将生成的SQL语句复制并粘贴到SSMS中，并确保最后删除尾随的 union (最后一个查询不会合并任何内容，如果您遇到错误，尝试使用结尾的 union 语句执行SQL.)

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

执行SQL，您应该获得视图名称的列表. Union 会自动消除重复项.

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.

要显示视图名称和列名称，您只需在 A 列中的单元格中添加另一个引用，即可提取您的列.另外，我们可以修改 WHERE 子句以仅查看 dbo 模式.

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.

对于第一个要更改为的公式( dbo 的schema_id为1):

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"

基本上将您在where子句中搜索的值添加到选择部分.

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"

这篇关于如何检查视图是否正在使用列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！