SQL Server recursive table valued function to find all required objects for an object

Do you have views built on views built on views? Would you like to analyze the dependency chain of some objects? The below function is a recursive table valued function that retrieves all required objects for the initial input view as well as the required objects for all objects down the chain.



CREATE FUNCTION [DBO].[GET_REQUIRED_OBJS] (@OBJ_SERVER_OR_NULL VARCHAR(2000),
@OBJ_DB VARCHAR(2000),
@OBJ_SCHEMA VARCHAR(2000),
@OBJ_NAME VARCHAR(2000),
@LVL INT)
RETURNS @RES TABLE (
OBJ_SERVER VARCHAR(2000)
,OBJ_DB VARCHAR(2000)
,OBJ_SCHEMA VARCHAR(2000)
,OBJ_NAME VARCHAR(2000)
,OBJ_TYPE VARCHAR(2000)
,LVL INT
)
--WITH ENCRYPTION|SCHEMABINDING, EXECUTE AS CALLER|SELF|OWNER|USER
AS

BEGIN
DECLARE @TEMP_RES TABLE (
OBJ_SERVER VARCHAR(2000)
,OBJ_DB VARCHAR(2000)
,OBJ_SCHEMA VARCHAR(2000)
,OBJ_NAME VARCHAR(2000)
,OBJ_TYPE VARCHAR(2000)
,LVL INT
);
DECLARE @VAL INT;
DECLARE @CUR_LVL INT = @LVL + 1;
DECLARE @CUR_OBJ_SERVER VARCHAR(2000)
DECLARE @CUR_OBJ_DB VARCHAR(2000)
DECLARE @CUR_OBJ_SCHEMA VARCHAR(2000)
DECLARE @CUR_OBJ_NAME VARCHAR(2000)
DECLARE @CUR_OBJ_TYPE VARCHAR(2000)
DECLARE MY_CURSOR CURSOR FOR

SELECT

SED.REFERENCED_SERVER_NAME
,SED.REFERENCED_DATABASE_NAME
,SED.REFERENCED_SCHEMA_NAME
,SED.REFERENCED_ENTITY_NAME
,COALESCE(OBJ_TYPE.TYPE_DESC, 'Other DB Object')
,@CUR_LVL
FROM SYS.SQL_EXPRESSION_DEPENDENCIES SED
JOIN SYS.OBJECTS O
ON (O.OBJECT_ID = SED.REFERENCING_ID)
JOIN SYS.SCHEMAS S
ON (S.SCHEMA_ID = O.SCHEMA_ID)
LEFT JOIN SYS.OBJECTS AS OBJ_TYPE
ON (OBJ_TYPE.OBJECT_ID = SED.REFERENCED_ID)
WHERE O.NAME = @OBJ_NAME
AND S.NAME = COALESCE(@OBJ_SCHEMA, 'dbo')

AND SED.REFERENCED_ENTITY_NAME != @OBJ_NAME

AND O.TYPE_DESC IN ('VIEW', 'SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION')

OPEN MY_CURSOR;

FETCH NEXT FROM MY_CURSOR INTO @CUR_OBJ_SERVER, @CUR_OBJ_DB, @CUR_OBJ_SCHEMA, @CUR_OBJ_NAME, @CUR_OBJ_TYPE, @LVL;

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO @TEMP_RES
SELECT
@CUR_OBJ_SERVER
,@CUR_OBJ_DB
,@CUR_OBJ_SCHEMA
,@CUR_OBJ_NAME
,@CUR_OBJ_TYPE
,@LVL


INSERT INTO @TEMP_RES
SELECT
*
FROM DBO.GET_REQUIRED_OBJS(@CUR_OBJ_SERVER, @CUR_OBJ_DB, @CUR_OBJ_SCHEMA, @CUR_OBJ_NAME, @LVL) GRO

;


FETCH NEXT FROM MY_CURSOR INTO @CUR_OBJ_SERVER, @CUR_OBJ_DB, @CUR_OBJ_SCHEMA, @CUR_OBJ_NAME, @CUR_OBJ_TYPE, @LVL;

END;

CLOSE MY_CURSOR;
DEALLOCATE MY_CURSOR;

INSERT INTO @RES
SELECT
OBJ_SERVER
,COALESCE(NULLIF(LTRIM(RTRIM(OBJ_DB)), ''), DB_NAME())
,COALESCE(NULLIF(LTRIM(RTRIM(OBJ_SCHEMA)), ''), 'dbo')
,OBJ_NAME
,OBJ_TYPE
,MAX(LVL)
FROM @TEMP_RES
GROUP BY OBJ_SERVER
,COALESCE(NULLIF(LTRIM(RTRIM(OBJ_DB)), ''), DB_NAME())
,COALESCE(NULLIF(LTRIM(RTRIM(OBJ_SCHEMA)), ''), 'dbo')
,OBJ_NAME
,OBJ_TYPE;

RETURN
END