How to get the stored procedure or views containing particular text from original SQL definition statements?

How to get the stored procedure or views containing particular text from original SQL definition statements?

Here we using following views.

sys.syscomments for SQL 2000 Compatibility -- Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements. For more information on sys.syscomments please go to http://msdn.microsoft.com/en-us/library/ms186293%28v=sql.100%29.aspx

Note:Microsoft recommend for any furture development do not use sys.syscomments view instead use sys.sql_modules.

sys.sql_modules For SQL 2005/SQL 2008.-- Returns a row for each object that is an SQL language-defined module.Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. Stand-alone defaults, objects of type D, also have an SQL module definition in this view. For a description of these types, see the type column in the sys.objectscatalog view for more information on sys.sql_modules please go to http://msdn.microsoft.com/en-us/library/ms175081%28v=sql.100%29.aspx

sys.sysobjects: Contains one row for each object that is created within a database, such as a constraint, default, log, rule, and stored procedure. For information on sys.sysobjects please go to http://msdn.microsoft.com/en-us/library/ms177596.aspx

SQL 2000	SQL 2005/SQL 2008
--to get the stored procedures containing Product in code USE AdventureWorks; GO select--top 10 *       name As Procedurename ,textAS ProcedureCode from       sys.syscomments sc inner join       sys.sysobjects so             on sc.id = so.id where       textlike'%Product%' and so.xtype ='P';	--to get the stored procedures containing Product in code USE AdventureWorks; GO select--top 10 *       name As Procedurename , sc.[definition] AS ProcedureCode from       sys.sql_modules sc inner join       sys.sysobjects so             on sc.object_id= so.id where       sc.[definition] like'%Product%' and so.xtype ='P';
--to get the Views containing Address in code USE AdventureWorks; GO select--top 10 *       name As Procedurename ,textAS ProcedureCode from       sys.syscomments sc inner join       sys.sysobjects so             on sc.id = so.id where       text like'%Address%' and so.xtype ='V';	--to get the Views containing Address in code USE AdventureWorks; GO select--top 10 *       name As Procedurename ,sc.[definition] AS ProcedureCode from       sys.sql_modules sc inner join       sys.sysobjects so             on sc.object_id= so.id where       sc.[definition] like'%Address%' and so.xtype ='V';