Post reply

Finding columns in all procedures

  • November 13, 2009 at 6:48 am

    #213968

    I have been tasked with finding out if a group of columns is used in any of my reports.

    All of my reports are in one DB. So, I was hoping to be able to search a sys table, that contains all of my procedures...to look to see if any of the list of columns is used in any of my reports.

    I don't know how to go about finding out this info..

  • November 13, 2009 at 7:15 am

    #1078907

    Hi,

    This query gives you the text used in the Stored Procedures.

    select * from sys.syscomments

    Filter the above query based on the requirements.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • November 13, 2009 at 7:23 am

    #1078913

    Thanks Adiga

  • November 13, 2009 at 7:27 am

    #1078918

    Make use of definition column under sys.sql_modules.

    http://msdn.microsoft.com/en-us/library/ms175081.aspx

    Manu

  • November 13, 2009 at 8:51 am

    #1078994

    I use this to search for character strings in stored procedures:

    select name, create_date, substring(definition,CHARINDEX('CharacterString', definition)-25,150) as 'Code Snippet', definition as 'Entire Code'

    from sys.sql_modules com

    join sys.objects obj on com.object_id = obj.object_id

    where definition like '%CharacterString%'

    order by name

    fyi: syscomments is a holdover from 2000

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

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