CODEWITHSUNDEEP
sqldatabasesql-server-2008ssms
mihsathe
mihsathe

Reputation: 9154

How to check if a view exists that uses a table

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

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

Upvotes: 3

Views: 856

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280340

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:

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.

Upvotes: 2

RBarryYoung
RBarryYoung

Reputation: 56735

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.

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.

Upvotes: 5

user704988
user704988

Reputation: 436

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' ;

Upvotes: 1

Related Questions