Post reply

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

  • 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

  • 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

  • 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

  • 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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply