Find occurence of any value in database!

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Pat Reddy » Find occurence of any value in database!

Find occurence of any value in database!

Rate Topic

Display Mode

Topic Options

Author

Message

Pat Reddy

Posted Saturday, December 29, 2007 7:35 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 04, 2012 6:39 PM
Points: 7, Visits: 165

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

Post #437367

Andrew McCrackin

Posted Monday, December 31, 2007 7:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 21, 2011 10:15 AM
Points: 3, Visits: 11

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

Post #437570

mgaert

Posted Monday, December 31, 2007 8:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 3:06 PM
Points: 7, Visits: 36

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!

Post #437583

mgaert

Posted Monday, December 31, 2007 8:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 3:06 PM
Points: 7, Visits: 36

Oops, I meant fails to COMPILE.

Post #437584

Sherman Meeds

Posted Monday, December 31, 2007 9:44 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, January 21, 2010 9:16 AM
Points: 46, Visits: 20

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.

Post #437593

mgaert

Posted Monday, December 31, 2007 11:20 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 3:06 PM
Points: 7, Visits: 36

Quite right! Thank you.

Post #437638

Angelindiego

Posted Thursday, September 11, 2008 10:09 AM

Old Hand

Group: General Forum Members
Last Login: Friday, April 12, 2013 11:22 AM
Points: 312, Visits: 386

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

Post #567897

Pat Reddy

Posted Thursday, September 11, 2008 10:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 04, 2012 6:39 PM
Points: 7, Visits: 165

I'm here! My address should be reddyss@charter.net
What's up?

Post #567919

gyessql

Posted Tuesday, February 17, 2009 11:45 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, April 27, 2011 12:59 AM
Points: 61, Visits: 898

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?

Post #659102

Pat Reddy

Posted Wednesday, February 18, 2009 6:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 04, 2012 6:39 PM
Points: 7, Visits: 165

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

Post #659286

« Prev Topic | Next Topic »

Permissions