Search code examples
sqlsql-serverdatabase-designviewssms

Is there a way to get the column usage of a view from multiple databases with INFORMATION_SCHEMA.VIEW_COLUMN_USAGE?

When I use:

select *
from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
WHERE VIEW_NAME = 'v_ExampleView'

I get the table and columns from the current database but there are tables outside of that database being used in the view. I have read-only access to our data warehouse and I am trying to get information on our views such as what tables they are pulling from and potentially what conditions are being applied. I am using SSMS. Any help would be much appreciated!

Solution

  • You can use the sys.dm_sql_referenced_entities function for this. Don't use INFORMATION_SCHEMA, it's for compatibility only.

    SELECT ed.*
FROM sys.dm_sql_referenced_entities('dbo.v_ExampleView', 'OBJECT');  --make sure to specify the schema