January 17, 2013 at 11:20 pm
Hi,
I want to write a Stored procedure that takes input - Table Name, SearchString
The SP should find the output the searchstring is available in which all columns for the particular table
Can anyone help me on this.
Regards,
Tony
January 18, 2013 at 1:15 am
You will need to do a link from sys.objects for the object in question to sys.columns link that back to sys.types and pull out any string based columns.
Then write a query which takes that output and builds the select statement up to search for the string.
Now is it going to be an open-ended like clause %searchstring%, if so that will not be SARGable and will cause your performance to drop dramatically as it will need to scan every row in the table.
You may want to look at full text indexing but doing that for each table and each string based column will be overkill.
http://www.sqlservercentral.com/scripts/Miscellaneous/31534/
January 18, 2013 at 7:31 am
aprabahar 48682 (1/17/2013)
Hi,I want to write a Stored procedure that takes input - Table Name, SearchString
The SP should find the output the searchstring is available in which all columns for the particular table
Can anyone help me on this.
Regards,
Tony
This topic comes up around here from time to time. There is absolutely no way to make this type of thing perform anything better than complete dismal. Unless you build a full text index for every single character based column in your database (which would be an absolutely horrible idea, don't even think about it) you are stuck with looking through every single column of every single row. What process could actually really need that sort of thing?
If this is a one time search for stuff that is another story, but as an ongoing process you are never going to find anything that will be acceptable.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply