Geek Answers Handbook

All dependency tree in SQL Server database

I need to maintain an old project without documentation, and there is a database with a lot of tables and functions and stored procedures. I want to build a dependency tree between tables and stored procedures, for example, this procedure executes these procedures and uses these tables.

  • Is there any tool that can help me

  • If this does not happen, what is the starting point for such algorithms? I mean that I can get the source code of all procedures from the database and parse it for all "exe #", "update #", "insert into #", etc. And in this case, what is the best way to solve this problem (maybe a regular expression or some special algorithms ...)?

+8
source share
5 answers

There are paid tools, such as redgate, but if you want, you can always right-click on the object and select “view dependencies”.

+5
source

Higarian, your code was very helpful to me. I clarified this a bit to remove circular dependencies, include table links, and sort by ObjectPath.

;with ObjectHierarchy ( Base_Object_Id , Base_Cchema_Id , Base_Object_Name , Base_Object_Type, object_id , Schema_Id , Name , Type_Desc , Level , Obj_Path) as ( select so.object_id as Base_Object_Id , so.schema_id as Base_Cchema_Id , so.name as Base_Object_Name , so.type_desc as Base_Object_Type , so.object_id as object_id , so.schema_id as Schema_Id , so.name , so.type_desc , 0 as Level , convert ( nvarchar ( 1000 ) , N'/' + so.name ) as Obj_Path from sys.objects so left join sys.sql_expression_dependencies ed on ed.referenced_id = so.object_id left join sys.objects rso on rso.object_id = ed.referencing_id where rso.type is null and so.type in ( 'P', 'V', 'IF', 'FN', 'TF' ) union all select cp.Base_Object_Id as Base_Object_Id , cp.Base_Cchema_Id , cp.Base_Object_Name , cp.Base_Object_Type , so.object_id as object_id , so.schema_id as ID_Schema , so.name , so.type_desc , Level + 1 as Level , convert ( nvarchar ( 1000 ) , cp.Obj_Path + N'/' + so.name ) as Obj_Path from sys.objects so inner join sys.sql_expression_dependencies ed on ed.referenced_id = so.object_id inner join sys.objects rso on rso.object_id = ed.referencing_id inner join ObjectHierarchy as cp on rso.object_id = cp.object_id and rso.object_id <> so.object_id where so.type in ( 'P', 'V', 'IF', 'FN', 'TF', 'U') and ( rso.type is null or rso.type in ( 'P', 'V', 'IF', 'FN', 'TF', 'U' ) ) and cp.Obj_Path not like '%/' + so.name + '/%' ) -- prevent cycles n hierarcy select Base_Object_Name , Base_Object_Type , REPLICATE ( ' ' , Level ) + Name as Indented_Name , SCHEMA_NAME ( Schema_Id ) + '.' + Name as object_id , Type_Desc as Object_Type , Level , Obj_Path from ObjectHierarchy as p order by Obj_Path
+8
source

Here Max (level) should solve your problem without the need for external tools

 ;WITH cte ( [ID] ,IDSchema,Nome,Tipo,level, SortCol) AS (SELECT [so].[object_id] AS ID ,so.[schema_id],so.[name],so.[type] ,0 AS [Level] ,CAST ([so].[object_id] AS VARBINARY (MAX)) AS SortCol FROM [sys].[objects] so LEFT JOIN sys.sql_expression_dependencies ed ON [ed].[referenced_id]=[so].[object_id] LEFT JOIN [sys].[objects] rso ON rso.[object_id]=[ed].referencing_id --in my database, if i insert tables on the search, it gets more tham 100 levels of recursivity, and that is bad WHERE [rso].[type] IS NULL AND [so].[type] IN ('V','IF','FN','TF','P') UNION ALL SELECT [so].[object_id] AS ID ,so.[schema_id],so.[name],so.[type] ,Level + 1 ,CAST (SortCol + CAST ([so].[object_id] AS BINARY (4)) AS VARBINARY (MAX)) FROM [sys].[objects] so INNER JOIN sys.sql_expression_dependencies ed ON [ed].[referenced_id]=[so].[object_id] INNER JOIN [sys].[objects] rso ON rso.[object_id]=[ed].referencing_id INNER JOIN cte AS cp ON rso.[object_id] = [cp].[ID] WHERE [so].[type] IN ('V','IF','FN','TF','P') AND ([rso].[type] IS NULL OR [rso].[type] IN ('V','IF','FN','TF','P')) ) --CTE SELECT ID, IDSchema, REPLICATE(' ',level)+nome AS Nome,'['+SCHEMA_NAME(IDSchema)+'].['+nome+']' AS Object,Tipo,Level,SortCol FROM cte AS p ORDER BY sortcol
+6
source

You can use sys.dm_sql_referenced_entities as shown below to get all the dependencies of any object

SELECT * FROM sys.dm_sql_referenced_entities('dbo.myStoredProcedure', 'OBJECT')

+5
source

Someone mentioned paid Redgate add-ons. ApexSQL is another company that creates add-ins for SQL Server. Some of them cost money, but others are free and still very useful.

ApexSQL Search is one of the free. These are two main functions:

  • Allows you to search for data or database structure for a specific text.
  • You can build a graphical dependency tree for one or more objects (what you need).

After installing it, simply right-click the object or database and click View dependencies (next to the icon next to it). Do not confuse it for the View dependencies (above, without the icon next to it), which is built into SQL Server. I believe that a hierarchical scheme should be most useful.

References:

0
source

More articles:


All Articles