August 12, 2011 at 1:24 am
Is there a way to search a string across values of all the columns of all the tables
August 12, 2011 at 1:55 am
Yes. Use INFORMATION_SCHEMA.COLUMNS to find out which columns hold character data, and then build some dynamic SQL to search in all of those columns. Beware! Performance will be horrible if you have a significant amount of data in your database. Alternatively, you may wish to investigate full-text indexing or third-party products.
John
August 12, 2011 at 2:06 am
Is there some query by which i can find the string in question ?
August 12, 2011 at 2:17 am
I'm sure if you really thought about it, you'd be able to answer that for yourself. Depends whether you want to find the text anywhere in the column's value or only want to match the complete value of the column. We'll assume the former, which will be much worse for performance:
SELECT Whatever
FROM MyTable
WHERE MyTextCol1 LIKE '%' + @SearchString + '%'
OR MyTextCol2 LIKE '%' + @SearchString + '%'
OR MyTextCol3 LIKE '%' + @SearchString + '%'
OR....
John
August 12, 2011 at 7:55 am
This topic came up a few months ago and I posted a really NASTY ugly cursor based dynamic sql solution that I created about a decade ago. Lowell also posted a version that was not too much different than mine. His did a few things differently than mine, but I can't remember the specifics. At any rate, below is the REALLY REALLY REALLY slow cursor based version. DO NOT run this on your production server unless it after hours. It will destroy performance of your server and it will take a LONG time to run. Test it on a dev server. If you need to find this on your production data it is highly recommended to make a backup and restore on a dev server and then run this. You will probably have to tweak this a bit for your needs. Good luck.
declare @table_name varchar(2000)
declare @sSQL nvarchar(4000)
declare @result varchar(20)
declare @column_name varchar(2000)
declare @SearchVal varchar(200)
set @SearchVal = '%your search val here%'
declare @ColName varchar (250)
set @ColName = '%use this if you want to limit to a naming convention on the columns to search (i.e. email)%'
declare SearchList cursor for
select distinct so.name,sc.name from syscolumns sc
inner join sysobjects so on sc.id = so.id
where sc.name like @ColName
and so.type = 'U'
open SearchList
fetch next from SearchList into @table_name, @column_name
while(@@fetch_status = 0)
begin
select @sSQL = 'if exists (select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''') select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''''
exec sp_executesql @sSQL
--select @ssql
fetch next from SearchList into @table_name, @column_name
end
close SearchList
deallocate SearchList
_______________________________________________________________
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/
August 12, 2011 at 8:17 am
Sean's so right on this...this is not a production tool.
if you have a MillionBillionRow table, and it has 15 varchar columns...it's going to tablescan that table 15 times.
this is the last version i built, it does it's best to try and limit the resulting perforamnce load:
CREATE PROCEDURE sp_UGLYSEARCH
/*
--usage:
-- EXEC sp_UGLYSEARCH 'Provisional'
-- EXEC sp_UGLYSEARCH 'TEST'
-- creates one SQL for each table i.e.
-- SELECT * FROM [ACACTSCR] WHERE [DESCRIP] LIKE '%TEST%' OR [TITLE] LIKE '%TEST%'
--optional parameter SEARCHBYCOLUMN
-- EXEC sp_UGLYSEARCH 'TEST',1
-- creates one SQL for each Column i.e.
-- SELECT * FROM [dbo].[ACACTSCR] WHERE [DESCRIP] LIKE '%TEST%'
-- SELECT * FROM [dbo].[ACACTSCR] WHERE [TITLE] LIKE '%TEST%'
*/
@SEARCHSTRING VARCHAR(50),
@SEARCHBYCOLUMN INT = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @sql VARCHAR(max),
@SCHEMANAME VARCHAR(100),
@TABLENAME VARCHAR(100),
@COLUMNNAME VARCHAR(100),
@COLZ VARCHAR(max)
CREATE TABLE #RESULTS(SCHEMANAME VARCHAR(100), TBLNAME VARCHAR(100),COLNAME VARCHAR(100),SQL VARCHAR(max))
SELECT
SCHEMA_NAME(schema_id) AS SCHEMANAME,
objz.name AS TBLNAME,
colz.name AS COLNAME,
TYPE_NAME(colz.user_type_id) AS DATATYPE
INTO #TEMP
FROM sys.objects objz
INNER JOIN sys.columns colz ON objz.object_id = colz.object_id
WHERE objz.type='U'
AND TYPE_NAME(colz.user_type_id) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
AND colz.max_length >= LEN(@SEARCHSTRING) --smart: don't search varchar(1) columns for 'TEST' 4xmpl
ORDER BY TBLNAME,COLNAME
IF @SEARCHBYCOLUMN = 0
BEGIN
DECLARE C1 CURSOR FOR
SELECT SCHEMANAME,TBLNAME,COLNAME FROM #TEMP ORDER BY SCHEMANAME,TBLNAME,COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @COLZ=''
SELECT @COLZ = @COLZ + QUOTENAME(COLNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'' OR ' FROM #TEMP WHERE TBLNAME=@TABLENAME
--@COLZ has a trailing 'OR ' which must be removed
SET @COLZ = SUBSTRING(@COLZ,1,LEN(@COLZ) -3)
--PRINT @COLZ
SET @sql = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + @COLZ + ') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''-'','' SELECT * FROM ' + QUOTENAME(@TABLENAME) + ' WHERE ' + REPLACE(@COLZ,'''','''''') + ''') ;'
--PRINT @sql
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
END
ELSE --@SEARCHBYCOLUMN <> 0
BEGIN
DECLARE C2 CURSOR FOR
SELECT SCHEMANAME,TBLNAME,COLNAME FROM #TEMP ORDER BY SCHEMANAME,TBLNAME,COLNAME
OPEN C2
FETCH NEXT FROM C2 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @sql = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'')
INSERT INTO #RESULTS(SCHEMANAME,TBLNAME,COLNAME,SQL) VALUES(''' + @SCHEMANAME + ''',''' + @TABLENAME + ''',''' + @COLUMNNAME + ''',''
SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''''%' + @SEARCHSTRING + '%'''' '') ;'
PRINT @sql
EXEC (@SQL)
FETCH NEXT FROM C2 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
END
CLOSE C2
DEALLOCATE C2
END --@SEARCHBYCOLUMN <> 0
SELECT * FROM #RESULTS ORDER BY TBLNAME,COLNAME
END --PROC
Lowell
August 12, 2011 at 8:23 am
--if you are going to put this in MASTER, and want it to be able to query
--each database's sys.columns, you MUST mark it as a system procedure:
--EXECUTE sp_ms_marksystemobject 'sp_UglySearch'
Lowell
August 22, 2011 at 4:18 pm
Having a quick look at the other comments this does seem to be the best option.....
John Mitchell-245523 (8/12/2011)
I'm sure if you really thought about it, you'd be able to answer that for yourself. Depends whether you want to find the text anywhere in the column's value or only want to match the complete value of the column. We'll assume the former, which will be much worse for performance:SELECT Whatever
FROM MyTable
WHERE MyTextCol1 LIKE '%' + @SearchString + '%'
OR MyTextCol2 LIKE '%' + @SearchString + '%'
OR MyTextCol3 LIKE '%' + @SearchString + '%'
OR....
John
The other code samples will tablescan a single table many times once per varchar column unless there are excessive indexes, whereas the above will only table scan once. You might need to use a couple of queries referencing info_schema objects to create the query rather than using dynamic sql.
If you need to do this on a regular basis as part of a business app then consider the above tied to FullText. That way you have less scanning to do but the downside is you will be adding another layer of complexity.
August 23, 2011 at 7:39 am
But this does not really solve the OP's question.
Is there a way to search a string across values of all the columns of all the tables
It is WAY faster and yes a better solution if you know the tables and columns. It does not however find a given value anywhere in your database. I can't imagine a situation other than a one shot where you would be trying to find a given value anywhere in your database. That is typically an analysis process and not something end users require.
_______________________________________________________________
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/
August 23, 2011 at 7:53 am
Sean Lange (8/23/2011)
It is WAY faster and yes a better solution if you know the tables and columns.
But what was quoted wasn't the whole solution. See my first post for the other half. It shouldn't be too difficult to find all the columns with character data and then build some dynamic SQL.
John
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply