Wednesday 22 February 2012

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';

No comments: