2. From the below query you can search for columns from the entire user defined tables of a particular database:
select * from sys.all_objects where type_desc = 'USER_TABLE'
in (select object_id from sys.all_columns where name like '%%')
3. From the below two queries you can search for any object (tables, columns, variables, etc.) from all the Stored Procedures of a particular database: First query is more efficient than second one but the execution time is little bit high.
a. select o.name, o.type_desc,* from sys.all_sql_modules m inner join sys.all_objects o on o.object_id = m.object_id
where m.definition like '%%' and o.type_desc = 'SQL_STORED_PROCEDURE' order by o.type_desc, o.name
ROUTINE_DEFINITION LIKE '%%'
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.