find all objects in database with column name

gistfile1.txt

--updated 6/27/2014 to include schema for each object

SELECT table_name AS [Table], column_name AS [Column], table_schema as [schema]
FROM information_schema.columns
WHERE column_name like '%' + @var + '%'
ORDER BY table_name, column_name
 
SELECT table_name as viewname, TABLE_SCHEMA as [schema]
FROM information_schema.views
WHERE view_definition like '%' + @var + '%'
ORDER BY table_name
 
SELECT s.name AS SchemaName, OBJECT_NAME(p.object_id) AS ProcName
FROM sys.procedures p
INNER JOIN sys.schemas s
	ON p.schema_id = s.schema_id
WHERE OBJECT_DEFINITION(p.object_id) LIKE '%' + @var + '%'
 
SELECT routine_name as [Function], routine_schema as [schema]
FROM information_schema.routines
WHERE routine_type='Function'
and routine_definition like '%' + @var + '%'
 
SELECT sys1.name trigger_name,
       sys2.name table_name,
	   s.name as [schema],
       c.text trigger_body,
       c.encrypted is_encripted,
       CASE
         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsTriggerDisabled') = 1
         THEN 0 ELSE 1
       END trigger_enabled,
       CASE
         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsInsertTrigger') = 1 THEN 'INSERT'
         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsUpdateTrigger') = 1 THEN 'UPDATE'
         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsDeleteTrigger') = 1 THEN 'DELETE'
       END trigger_event,
       CASE WHEN OBJECTPROPERTY(sys1.id, 'ExecIsInsteadOfTrigger') = 1
         THEN 'INSTEAD OF' ELSE 'AFTER'
       END trigger_type
  FROM sysobjects sys1
  JOIN sysobjects sys2 ON sys1.parent_obj = sys2.id
  JOIN syscomments c ON sys1.id = c.id
  JOIN sys.tables t ON sys1.parent_obj = t.object_id
  JOIN sys.schemas s ON t.schema_id = s.schema_id
 WHERE sys1.xtype = 'TR'
and c.[text] like '%' + @var + '%'