Clever Geek Handbook

How to check if a view that uses a table exists

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

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

+3


source to share


3 answers


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 with any ISO SQL compliant database, not just SQL Server.

Note that database dependencies are a different matter. In theory, they should appear here, however in practice this can be inconsistent as SQL Server makes deferred resolution possible even for views when it comes to links between databases.

+5


source 


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

, and so on, are all exposed crash synchronization. More information here:

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

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 do the following it will return the lines again:

EXEC sp_refreshsqlmodule N'dbo.view1';

But who wants to update every view on the system every time you want to check the metadata?

So you can combine this method with text parsing for all of your views:

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

This can lead to false positives depending on the name of your table, but it's probably a good cross-validation.

To avoid this kind of problem, I tried to get in the habit of creating my views WITH SCHEMABINDING

(or just avoiding views as much as possible). Of course, it can become a pain when you need to modify the table in a way that does not affect the view, but in any case, the changes to the table should be taken seriously.

+2


source


For the same database, you can check the dependencies for this table and see what other objects are using it.

EXEC sp_depends @objname = N'your_table_name' ;
+1


source






More articles:


All Articles