How do you search SQL Server for sp/func/trig source?

• Will DOugherty

  Old Hand

  Points: 368

  More actions

  October 5, 2006 at 5:58 am

  #103252

  Hi,

  In Oracle there are dictionary views named DBA_SOURCE and USER_SOURCE that show all/your source lines for all of the stored procedures, functions, triggers, etc. They are very useful when you want to see which program objects will be affected by a change. Is there an equivalent in SQL Server?

  ---

  Thanks ,

  Will Dougherty

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  October 5, 2006 at 6:23 am

  #664280

  select * from syscomments will show you all source code from procs, views, functions etc

  or join it on sysobjects

  select o.id,o.name,c.text from syscomments c inner join sysobjects o on o.id = c.id

  MVDBA

• Will DOugherty

  Old Hand

  Points: 368

  More actions

  October 5, 2006 at 7:46 am

  #664309

  Thanks Mike.  Now that I hear it, I realize that I used to know it.

  Will

  ---

  Thanks ,

  Will Dougherty

• Ian Griffiths

  Ten Centuries

  Points: 1025

  More actions

  October 6, 2006 at 2:47 am

  #664531

  Theres also INFORMATION_SCHEMA.ROUTINES view which you can get at least stored procedure and function names and definitions from.  All the INFORMATION_SCHEMA views are supposed to be ANSI SQL-92 compliant, which means the same or similar queries should work in any database.  The same isn't guaranteed of system views like syscomments etc.