In SQL Server 2005 you can see which columns and which tables a View uses. There is a System-View in every database for this. This view is created automatically.
Best practice is to create a view per report / ETL package, so you have the logic in one place. With the query below you can easily assess the impact of a change, because you quickly see where the columns are used in the Data Warehouse!
SELECT *FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGEFrequently asked questions
How do I see which columns a view uses in SQL Server?
Use the system view INFORMATION_SCHEMA.VIEW_COLUMN_USAGE. It shows which columns and tables a view uses. Every database has this view, and SQL Server creates it automatically.
How do I assess the impact of a column change in a data warehouse?
Query INFORMATION_SCHEMA.VIEW_COLUMN_USAGE. That way you quickly see in which views the column is used in the data warehouse.
Does INFORMATION_SCHEMA.VIEW_COLUMN_USAGE work in SQL Server 2005?
Yes. In SQL Server 2005 you can use this system view to see which columns and tables a view uses.
Do I have to create INFORMATION_SCHEMA.VIEW_COLUMN_USAGE myself?
No. This view is in every database and is created automatically. You only run a SELECT.
What is a good way to use views for reports and ETL?
Create a separate view for every report or ETL package. Then the logic is in one place and you can easily assess the impact of changes.
Comments
Related blogs
Recursive Query with CTE II
Searching for text in SSIS packages
Date calculations