Find occurence of any value in database!

  • Comments posted to this topic are about the item Find occurence of any value in database!

  • just some notes:

    Occurrences might be misleading since it's a count of syscomments records associated with the object that contain the search string rather than a count of instances of that search string in those entries.

    Also, you might want to add to the type list the following

    WHEN SO.xtype = 'TF' THEN 'Table Function - early binding' -- table defined in header

    WHEN SO.xtype = 'IF' THEN 'Table Function - late binding' -- table, undefined

    WHEN SO.xtype = 'FN' THEN 'Scalar Function'

    ELSE '*** ' + SO.xtype + ' ***'


  • The script fails to post in my SQL 2005 studio with the follow errors (fyi CREATE is on the first line):

    Msg 102, Level 15, State 1, Procedure spUtil_Occur, Line 3

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Procedure spUtil_Occur, Line 39

    Must declare the scalar variable "@SearchString".

    Looks OK to me. Am I missing something?

    Thanks for the post!

  • Oops, I meant fails to COMPILE.

  • When I attempted to insert my version of the stored procedure in Sql Server 2005, it came up with the same error messages detailed above. I retyped the third line (@SearchString SQL_VARIANT) with a tab as the first character and it complied properly. Evidently, there are embedded invisible characters in the text when copied directly from the article.

  • Quite right! Thank you.

  • I wanted to ask Pat a question and the email he provided in the article isn't valid anymore. Pat are you out there??? Please post a new email address.....

    Thank you!!,


  • I'm here! My address should be

    What's up?

  • Does this stored proc really give the occurence of a value in the database or the occurence of a attribute/field name in the database ?

    I have a value and I need to check all the tables in the database where this value is present. Is there a way to get all the tables and column names in which the value is present?

  • Yes, it finds the value in the data by exact match or LIKE, depending on the parameter sent in. E-mail me directly at for the code.


  • Thanks for the script.

Viewing 11 posts - 1 through 10 (of 10 total)

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