Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Search all columns in every table for a value Expand / Collapse
Author
Message
Posted Tuesday, November 9, 2010 8:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 5, 2014 3:04 PM
Points: 32, Visits: 65
the only thing i'd like to mention is regarding the convert in the where clause. if you don't specify the length when you convert the current search column to varchar it, by default, will truncate the column value to a length of 30. it will also trim trailing spaces, so it would really only search the first 30 characters of the current column. see my example below:

DECLARE @Value varchar(100)='12345678901234567890123456789012345678901234567890'
SELECT @Value, LEN(@Value), LEN(CAST(@Value AS varchar)), LEN(CONVERT(varchar, @Value)), LEN(CAST(@Value AS varchar(1000))), LEN(CONVERT(varchar(1000), @Value))
SET @Value=REPLACE(@Value, '0',' ')
SELECT @Value, LEN(@Value), LEN(CAST(@Value AS varchar)), LEN(CONVERT(varchar, @Value)), LEN(CAST(@Value AS varchar(1000))), LEN(CONVERT(varchar(1000), @Value))

Post #1017947
Posted Friday, November 12, 2010 12:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 1,038, Visits: 1,306
Thanks @Adam Gojdas & Author.
It helps me to solve one issue.


Thanks
Post #1019739
Posted Wednesday, May 25, 2011 8:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 14, 2014 1:19 PM
Points: 323, Visits: 1,456
Yes, thanks to Michael and others for this. Helped me quickly find an errant value in a field during a very large insert that failed.

Ken
Post #1114782
Posted Friday, December 7, 2012 12:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 21, 2013 11:39 AM
Points: 7, Visits: 85
I created this query for the same purpose:
[url=http://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns][/url]
Post #1394192
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse