SELECT DISTINCT O.NAME,
CASE
WHEN O.XTYPE = 'P' THEN 'PROCEDURE'
WHEN O.XTYPE = 'V' THEN 'VIEW'
WHEN O.XTYPE = 'TR' THEN 'TRIGGER'
ELSE O.XTYPE
END AS TYPE
FROM SYSCOMMENTS C
INNER JOIN SYSOBJECTS O ON C.ID = O.ID
WHERE C.TEXT LIKE '%table_name%'
The SQL query retrieves distinct object names (OBJ.NAME) and their types (TYPE) based on their definitions stored in the SYSCOMMENTS and SYSOBJECTS system tables. It uses a CASE statement to categorize objects based on their XTYPE values ('P' for procedure, 'V' for view, 'TR' for trigger, and otherwise displaying the XTYPE directly).
The SYSCOMMENTS table stores the text of definitions for various database objects such as procedures, triggers, functions, and views.
The SYSOBJECTS table stores metadata about various objects within the database, including their names, types, and other properties.
Query File Attached.