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

Search All Columns in all tables Expand / Collapse
Author
Message
Posted Sunday, September 23, 2007 9:09 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:20 AM
Points: 263, Visits: 275
Comments posted to this topic are about the item Search All Columns in all tables

SQL guy and Houston Magician
Post #401806
Posted Wednesday, January 6, 2010 5:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 6, 2012 5:18 AM
Points: 73, Visits: 30
The actual post is Ok for the tables which are of One word for others below is the modified query.

DECLARE @searchSQL AS VARCHAR(MAX)
DECLARE @SearchItem AS VARCHAR(MAX) ; SET @SearchItem = '%Davolio%'

SELECT @searchSQL = COALESCE(@searchSQL + '+ CAST(COALESCE(MIN(CASE WHEN IC.Ordinal_position = ' +
CAST(Ordinal_position AS VARCHAR(MAX)) +
' THEN '' OR '' + IC.Column_Name + '' LIKE ''''' + @SearchItem + ''''''' END), '''') AS NVARCHAR(max))' +
CHAR(13), 'CAST(COALESCE(MIN(CASE WHEN IC.Ordinal_position = ' +
CAST(Ordinal_position AS VARCHAR(MAX)) +
' THEN IC.Column_Name + '' LIKE ''''' + @SearchItem + ''''''' END), '''') AS NVARCHAR(max))' + CHAR(13))
FROM INFORMATION_SCHEMA.Columns
GROUP BY Ordinal_Position
ORDER BY Ordinal_Position

EXEC('SELECT REPLACE(Query, ''WHERE OR'', ''WHERE'') FROM
(SELECT ''SELECT '''''' + CAST(TABLE_NAME AS VARCHAR(MAX)) + '''''' as tbl, * FROM ['' +
CAST(TABLE_SCHEMA AS VARCHAR(MAX)) + '']. ['' + CAST(TABLE_NAME AS VARCHAR(MAX)) + ''] WHERE '' + ' + @searchSQL + ' AS query
FROM INFORMATION_SCHEMA.Columns IC
WHERE DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'')
GROUP BY TABLE_NAME, TABLE_SCHEMA) S')
Post #842700
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse