Tuesday, 27 September 2016

How to Find In Valid Object Name SQL Server

SELECT   STORED_PROCEDURE = QUOTENAME(SCHEMA_NAME(O.[SCHEMA_ID])) + '.' + QUOTENAME(O.NAME),
  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