I would like to give you a simple query to find out objects where view depends on either directly or in-directly.
so Lets create few tables and views to test the query.
-- To Create EMPLOYEE Table
CREATE TABLE [dbo].[EMPLOYEE](
[EMP_ID] [int] IDENTITY(1,1) NOT NULL,
[FIRST_NAME] [varchar](30) NULL,
[MIDLE_NAME] [varchar](30) NULL,
[LAST_NAME] [varchar](30) NULL,
[SALARY] [bigint] NULL,
[DEPARTMENT_ID] [int] NULL
)
-- To Create DEPARTMENT table
CREATE TABLE [dbo].[DEPARTMENT](
[DEPARTMENT_ID] [int] IDENTITY(10,10) NOT NULL,
[DEPARTMENT_NAME] [varchar](30) NULL
)
-- Test View 1
CREATE VIEW V_Emp_Info as
SELECT E.*,( SELECT D.DEPARTMENT_NAME FROM DEPARTMENT D WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID ) AS DEPT_NAME
FROM EMPLOYEE E
-- Test View 2
CREATE VIEW V_EMP_TEST AS
SELECT V.* FROM EMPLOYEE E, V_EMP_INFO V
WHERE E.FIRST_NAME = V.FIRST_NAME
-- Query to get all the objects on this this view depends either directly or indirectly.
WITH Depends_CTE AS (
SELECT VIEW_SCHEMA,TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME ,VIEW_NAME, CAST('Directly' AS VARCHAR(30)) as How
FROM Information_Schema.View_Table_Usage
WHERE VIEW_NAME = 'V_EMP_TEST'
UNION ALL
SELECT e.VIEW_SCHEMA,e.TABLE_CATALOG,e.TABLE_SCHEMA,e.TABLE_NAME ,e.VIEW_NAME,CAST('In-Directly' AS VARCHAR(30)) AS HOW
FROM Information_Schema.View_Table_Usage e
INNER JOIN Depends_CTE d ON e.VIEW_NAME = d.TABLE_NAME
)
SELECT *
FROM Depends_CTE
Just pass the view name to the query.
This implementation is using Recursive CTEs (Common Table Expressions).
Wednesday, July 8, 2009
Find out the objects on which the view depends either directly or indirectly
Labels:
DotNetVJ,
Vijaya Kadiyala
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment