Find occurence of any value in database!

  • Pat Reddy

    SSC Enthusiast

    Points: 107

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

  • Andrew McCrackin

    SSC Enthusiast

    Points: 129

    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 + ' ***'

    Thanks

  • mgaert

    SSC Journeyman

    Points: 91

    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!

  • mgaert

    SSC Journeyman

    Points: 91

    Oops, I meant fails to COMPILE.

  • Sherman Meeds

    Valued Member

    Points: 68

    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.

  • mgaert

    SSC Journeyman

    Points: 91

    Quite right! Thank you.

  • Angelindiego

    SSCertifiable

    Points: 6048

    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!!,

    Angelindiego

  • Pat Reddy

    SSC Enthusiast

    Points: 107

    I'm here! My address should be reddyss@charter.net

    What's up?

  • gyessql

    Ten Centuries

    Points: 1097

    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?

  • Pat Reddy

    SSC Enthusiast

    Points: 107

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

    Pat

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

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

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