SELECT STORED_PROCEDURE = QUOTENAME(SCHEMA_NAME(O.[SCHEMA_ID])) + '.' + QUOTENAME(O.NAME),
OBJ_TYPE = O.TYPE_DESC,
D.REFERENCED_ENTITY_NAME
FROM SYS.SQL_EXPRESSION_DEPENDENCIES D
JOIN SYS.OBJECTS O
ON D.REFERENCING_ID = O.[OBJECT_ID]
WHERE D.IS_AMBIGUOUS = 0
AND D.REFERENCED_ID IS NULL
AND D.REFERENCED_SERVER_NAME IS NULL -- IGNORE OBJECTS FROM LINKED SERVER
AND CASE D.REFERENCED_CLASS -- IF DOESN’T EXIST
WHEN 1 -- OBJECT
THEN OBJECT_ID(
ISNULL(QUOTENAME(D.REFERENCED_DATABASE_NAME), DB_NAME()) + '.' +
ISNULL(QUOTENAME(D.REFERENCED_SCHEMA_NAME), SCHEMA_NAME()) + '.' +
QUOTENAME(D.REFERENCED_ENTITY_NAME))
WHEN 6 -- OR USER DATATYPE
THEN TYPE_ID(
ISNULL(D.REFERENCED_SCHEMA_NAME, SCHEMA_NAME()) + '.' + D.REFERENCED_ENTITY_NAME)
WHEN 10 -- OR XML SCHEMA
THEN (SELECT
1
FROM SYS.XML_SCHEMA_COLLECTIONS X
WHERE X.NAME = D.REFERENCED_ENTITY_NAME
AND X.[SCHEMA_ID] = ISNULL(SCHEMA_ID(D.REFERENCED_SCHEMA_NAME), SCHEMA_ID()))
END IS NULL
OBJ_TYPE = O.TYPE_DESC,
D.REFERENCED_DATABASE_NAME,s
D.REFERENCED_SCHEMA_NAME,D.REFERENCED_ENTITY_NAME
FROM SYS.SQL_EXPRESSION_DEPENDENCIES D
JOIN SYS.OBJECTS O
ON D.REFERENCING_ID = O.[OBJECT_ID]
WHERE D.IS_AMBIGUOUS = 0
AND D.REFERENCED_ID IS NULL
AND D.REFERENCED_SERVER_NAME IS NULL -- IGNORE OBJECTS FROM LINKED SERVER
AND CASE D.REFERENCED_CLASS -- IF DOESN’T EXIST
WHEN 1 -- OBJECT
THEN OBJECT_ID(
ISNULL(QUOTENAME(D.REFERENCED_DATABASE_NAME), DB_NAME()) + '.' +
ISNULL(QUOTENAME(D.REFERENCED_SCHEMA_NAME), SCHEMA_NAME()) + '.' +
QUOTENAME(D.REFERENCED_ENTITY_NAME))
WHEN 6 -- OR USER DATATYPE
THEN TYPE_ID(
ISNULL(D.REFERENCED_SCHEMA_NAME, SCHEMA_NAME()) + '.' + D.REFERENCED_ENTITY_NAME)
WHEN 10 -- OR XML SCHEMA
THEN (SELECT
1
FROM SYS.XML_SCHEMA_COLLECTIONS X
WHERE X.NAME = D.REFERENCED_ENTITY_NAME
AND X.[SCHEMA_ID] = ISNULL(SCHEMA_ID(D.REFERENCED_SCHEMA_NAME), SCHEMA_ID()))
END IS NULL
No comments:
Post a Comment