Nifty!

As a rule, I need to know where the columns are being used. Since the primary server has over 180000 objects on it of many different types, I need to know more than procedures. So, here is my take on your query.

WITH TableList_CTE (TableName)

AS

(

SELECT TABLE_NAME + CHAR(32) AS TableName

FROM INFORMATION_SCHEMA.TABLES T

WHERE t.TABLE_TYPE = 'BASE TABLE'

)

SELECT TableName, OBJECT_NAME(OBJECT_ID) AS ProcedureName,

CASE WHEN OBJECTPROPERTY(OBJECT_ID, 'IsReplProc') = 1 THEN 'Replication Stored Procedure'

WHEN OBJECTPROPERTY(OBJECT_ID, 'IsExtendedProc') = 1 THEN 'Extended Stored Procedure'

WHEN OBJECTPROPERTY(OBJECT_ID, 'IsProcedure') = 1 THEN 'Stored Procedure'

WHEN OBJECTPROPERTY(OBJECT_ID, 'IsTrigger') = 1 THEN 'Trigger'

WHEN OBJECTPROPERTY(OBJECT_ID, 'IsTableFunction') = 1 THEN 'Table-Valued Function'

WHEN OBJECTPROPERTY(OBJECT_ID, 'IsScalarFunction') = 1 THEN 'Scalar-Valued Function'

WHEN OBJECTPROPERTY(OBJECT_ID, 'IsInlineFunction') = 1 THEN 'Inline function'

WHEN OBJECTPROPERTY(OBJECT_ID, 'IsView') = 1 THEN 'View'

ELSE 'Unknown' END AS ProcedureType

FROM sys.sql_modules S

JOIN TableList_CTE ON 1 = 1

ORDER BY TableName