如何检查是否存在使用表的视图

Is it possible to check if a table is part of a view in same or different database using SQL Server Management Studio?

是否可以使用SQL Server Management Studio检查表是否是相同或不同数据库中视图的一部分?

If it can be done through some plugins, that would be fine too.

如果可以通过一些插件完成,那也没关系。

3 个解决方案

#1

Like this:

SELECT  *
FROM    INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE   TABLE_SCHEMA = 'dbo'    --(or whatever your Schema name is)
  AND   TABLE_NAME   = 'YourTableName'

Should work on any ISO SQL compliant database, not just SQL Server.

应该适用于任何符合ISO SQL的数据库,而不仅仅是SQL Server。

Note that cross-database dependencies are another matter. In theory, they should show up here however, in practice this may be inconsistent because SQL Server does allow deferred resolution, even for Views, when it comes to cross-database references.

请注意,跨数据库依赖性是另一回事。理论上,它们应该出现在这里,但实际上这可能是不一致的,因为当涉及到跨数据库引用时,SQL Server确实允许延迟解析,即使对于Views也是如此。

#2

SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
  + '.' + QUOTENAME(OBJECT_NAME([object_id]))
  FROM sys.sql_dependencies
  WHERE referenced_major_id = OBJECT_ID(N'dbo.your_table_name');

Or:

SELECT referencing_schema_name, referencing_entity_name
  FROM sys.dm_sql_referencing_entities(N'dbo.your_table_name', N'OBJECT');

However note that some of these methods, including sp_depends, INFORMATION_SCHEMA, sysdepends etc. are all prone to falling out of sync. More information here:

但请注意,其中一些方法(包括sp_depends,INFORMATION_SCHEMA,sysdepends等)都容易失去同步。更多信息:

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008.aspx

A quick example:

一个简单的例子:

CREATE TABLE dbo.table1(id INT);
GO
CREATE VIEW dbo.view1 
AS
  SELECT id FROM dbo.table1;
GO

SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
  + '.' + QUOTENAME(OBJECT_NAME([object_id]))
  FROM sys.sql_dependencies
  WHERE referenced_major_id = OBJECT_ID('dbo.table1');

-- returns 1 row

GO    
DROP TABLE dbo.table1;
GO
CREATE TABLE dbo.table1(id INT);
GO

SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
  + '.' + QUOTENAME(OBJECT_NAME([object_id]))
  FROM sys.sql_dependencies
  WHERE referenced_major_id = OBJECT_ID('dbo.table1');

-- returns 0 rows!!!!

If you execute the following, it will return rows again:

如果执行以下操作,它将再次返回行:

EXEC sp_refreshsqlmodule N'dbo.view1';

But who wants to be refreshing every view in the system, every time you want to check the metadata?

但是,每当您想要检查元数据时,谁想要刷新系统中的每个视图?

So you may want to combine this method with brute force parsing of the text for all your views:

因此,您可能希望将此方法与对所有视图的文本的强力解析相结合:

SELECT name FROM sys.views
  WHERE OBJECT_DEFINITION([object_id])
  LIKE N'%your_table_name%';

That is liable to get some false positives depending on the name of your table, but it's probably a good cross-check.

根据你的桌子名称,这可能会产生一些误报,但这可能是一个很好的交叉检查。

To avoid this kind of issue, I've tried to get into the habit of creating my views WITH SCHEMABINDING (or just avoiding views as much as possible). Sure, that can become a pain when you need to change the table in a way that doesn't affect the view, but table changes should be taken seriously anyway.

为了避免这种问题,我试图养成使用SCHEMABINDING创建视图的习惯(或者尽可能地避免视图)。当然,当您需要以不影响视图的方式更改表格时,这可能会变得很痛苦,但无论如何都应该认真对待表格更改。

#3

For same databse, you can check dependencies for that table and see what other objects uses it.

对于相同的数据库,您可以检查该表的依赖关系并查看其他对象使用它。

EXEC sp_depends @objname = N'your_table_name' ;

#1