How can i check sql server 'views' dependencies
Is there a way that i can find out wh开发者_如何学Cat base tables are being used by views using a custom query or stored procedure?
You could use the sys.dm_sql_referenced_entities function to find objects referenced by a specified view:
SELECT DISTINCT
referenced_schema_name ,
referenced_entity_name
FROM sys.dm_sql_referenced_entities ('Sales.vSalesPersonSalesByFiscalYears', 'OBJECT');
Also, there is the sys.sql_expression_dependencies system view where you can specify a table name and a type of the referencing object:
SELECT
referencing_object_name = o.name,
referencing_object_type_desc = o.type_desc
FROM sys.sql_expression_dependencies se
INNER JOIN sys.objects o
ON se.referencing_id = o.[object_id]
WHERE referenced_entity_name = 'Person' AND o.type_desc = 'View'
To avoid "manual" work you could also use ApexSQL Clean, a SQL Server tool that can find all internal and external dependencies. In the results pane select an object and see all objects that depend on the selected object, and objects on which the selected object depends on:
You can also filter objects and visualize dependencies:
Disclaimer: I work for ApexSQL as a Support Engineer
Hope this helps
You could use the sql_dependencies view:
select OBJECT_NAME(referenced_major_id) as DependantObject
from sys.sql_dependencies
where object_id = object_id('YourViewName')
To recursively retrieve dependencies (f.e., if you select from a view, this would find the tables that the other view references):
with deps (child, parent) as (
select d.object_id, d.referenced_major_id
from sys.sql_dependencies d
where d.object_id = object_id('YourViewName')
union all
select d.object_id, d.referenced_major_id
from sys.sql_dependencies d
inner join deps on deps.parent = d.object_id
)
select OBJECT_NAME(parent)
from deps
This method is not fool-proof. For example, if you rename an object using sp_rename, its dependencies are not updated.
If this is something you'll be doing often Red Gate SQL Dependency Tracker (no I don't work for them) is a great tool. I think they have a trial period if you want to try it.
Here's a good explanation:
https://web.archive.org/web/1/http://blogs.techrepublic%2ecom%2ecom/datacenter/?p=277
You can do that using sys.sql_expression_dependencies in SQL-Server >= 2008 (R1+)
Note: This will take 30 seconds or more.
If you only want the views, remove the scalar + table-valued functions ( 'FN' and 'IF').
A table-valued function can be a dependency of a view and vice-versa, plus the view/tvf may depend on scalar functions.
;WITH TFV_Dependencies AS
(
SELECT
--OBJECT_SCHEMA_NAME(sysdep.referencing_id) AS Referencing_Schema
ReferencingO.object_id AS ReferencingObject_Id
,sysschema.name AS Referencing_Schema
,ReferencingO.name AS Referencing_Object_Name
,sysdep.referenced_schema_name AS Referenced_Schema
,sysdep.referenced_entity_name AS Referenced_Object_Name
--,ReferencingO.type
,ReferencedO.name AS RefName
--,ReferencedO.type AS RefType
FROM sys.objects as ReferencingO
LEFT JOIN sys.schemas AS sysschema
ON sysschema.schema_id = ReferencingO.schema_id
LEFT JOIN sys.sql_expression_dependencies AS sysdep
LEFT JOIN sys.objects as ReferencedO
ON ReferencedO.name = sysdep.referenced_entity_name
ON sysdep.referencing_id = ReferencingO.object_id
AND ReferencingO.name <> sysdep.referenced_entity_name
--AND ReferencedO.type <> 'U'
AND ReferencedO.type IN ( 'IF', 'FN', 'V' )
WHERE (1=1)
--AND ReferencingO.type NOT IN ('PK','F','UQ','SQ','D','IT', 'S')
--AND ReferencingO.type IN ( 'U', 'V', 'FN', 'IF', 'P')
AND ReferencingO.type IN ( 'IF', 'FN', 'V' )
AND ReferencingO.name NOT IN
(
'dt_adduserobject'
,'dt_droppropertiesbyid'
,'dt_dropuserobjectbyid'
,'dt_generateansiname'
,'dt_getobjwithprop'
,'dt_getobjwithprop_u'
,'dt_getpropertiesbyid'
,'dt_getpropertiesbyid_u'
,'dt_setpropertybyid'
,'dt_setpropertybyid_u'
,'dt_verstamp006'
,'dt_verstamp007'
,'sp_helpdiagrams'
,'sp_creatediagram'
,'sp_alterdiagram'
,'sp_renamediagram'
,'sp_dropdiagram'
,'sp_helpdiagramdefinition'
,'fn_diagramobjects'
)
)
,DependencyGroup AS
(
-- base case
SELECT
ReferencingObject_Id AS ObjectId
,Referencing_Object_Name AS ObjectName
,Referencing_Schema AS ObjectSchema
,1 AS Lvl
FROM TFV_Dependencies
WHERE 1=1
AND Referenced_Object_Name IS NULL
-- recursive case
UNION ALL
SELECT
d.ReferencingObject_Id AS ObjectId
,d.Referencing_Object_Name AS ObjectName
,d.Referencing_Schema AS ObjectSchema
,r.Lvl + 1 AS Lvl
FROM TFV_Dependencies AS d
INNER JOIN DependencyGroup AS r
ON r.ObjectName = d.Referenced_Object_Name
)
,
CTE AS
(
SELECT TOP 999999999999999999
MAX(Lvl) AS Lvl
,ObjectId
,ObjectSchema
,ObjectName
--,'DELETE FROM [' + REPLACE(ObjectName, '''', '''''') + ']; ' AS DeleteCmd
FROM DependencyGroup
GROUP BY ObjectId, ObjectSchema, ObjectName
)
SELECT
CTE.Lvl
,CTE.ObjectSchema AS SPECIFIC_SCHEMA
,CTE.ObjectName AS SPECIFIC_NAME
,sysSqlModules.definition AS ROUTINE_DEFINITION
FROM CTE
LEFT JOIN sys.sql_modules AS sysSqlModules
ON sysSqlModules.object_id = CTE.ObjectId
ORDER BY Lvl, ObjectSchema, ObjectName
OPTION (MAXRECURSION 0)
精彩评论