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»»

Searching Database tables for a specific value within a database field Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 6:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 12:16 PM
Points: 4, Visits: 16
Hi,

I'm new to SQL Server and I need to find out if the value 'Criminal' is stored in any of the Column/Fields within the database. Is there a way to do this with a Select statement?

This is as far as I have gotten but this Select will only give me columns name Criminal not the value within the column name.

select colmun_name, table_name
from Information_Schema.columns
where column_name has 'Criminal' in it.

Thanks Much, Jim
Post #1442716
Posted Tuesday, April 16, 2013 7:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
jimoa312 (4/16/2013)
Hi,

I'm new to SQL Server and I need to find out if the value 'Criminal' is stored in any of the Column/Fields within the database. Is there a way to do this with a Select statement?

This is as far as I have gotten but this Select will only give me columns name Criminal not the value within the column name.

select colmun_name, table_name
from Information_Schema.columns
where column_name has 'Criminal' in it.

Thanks Much, Jim


This request comes up around here about twice a month. I will post some code I wrote for this very thing a number of years ago. There is VERY VERY VERY IMPORTANT condition with this code. DO NOT RUN THIS IN PRODUCTION!!!!!!! The logic of what you have to do here means that we have to look in every single column of every single row of every single table. This is horribly slow and incredibly inefficient. There is no good way to do this. I have seen this run for over 24 hours on a database in the past. Run this ONLY ON A DEV/TEST server!!!!


One last thing...did I mention that you should not run this in production???

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

Lowell another long time poster around here has a stored proc that does this same kind of thing. His does some things mine doesn't. Depending on your requirements one or the other may be better suited. The same caveat about not running in production applies here too.

CREATE PROCEDURE sp_UGLYSEARCH 
/*
--Purpose: to search every string column in a databasefor a specific word
--returns sql statement as a string which idnetifies the matching table
-- or when the optional parameter is used, the sql statement for the specific matching column.
--usage:
-- EXEC sp_UGLYSEARCH 'Provisional'
-- EXEC sp_UGLYSEARCH 'TEST'
-- creates one SQL for each table that actually has a match for the searched value 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 that actually has a match for the searched value 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
GO

SELECT * FROM #RESULTS ORDER BY TBLNAME,COLNAME
END --PROC
GO

Happy hunting!!!


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1442736
Posted Tuesday, April 16, 2013 7:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
Sean Lange (4/16/2013)
[quote]jimoa312 (4/16/2013)

... DO NOT RUN THIS IN PRODUCTION!!!!!!!
...Run this ONLY ON A DEV/TEST server!!!!


One last thing...did I mention that you should not run this in production???

Happy hunting!!!


you should probably mention he shouldn't run this on production :)


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1442741
Posted Tuesday, April 16, 2013 7:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 12:16 PM
Points: 4, Visits: 16
Thanks for the infomation. I will not run it in Production.
Post #1442742
Posted Tuesday, April 16, 2013 7:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
--Variable Delaration
Declare @keytosearch varchar(max), @Database_Selected varchar(50)

set @keytosearch ='%london%'
set @Database_Selected= 'Northwind'

Declare @Table varchar(100), @Table_Name Cursor, @Count_Column int,
@Result nvarchar(4000),@ID int,@ID_inserted int,@Count_Table int, @data_type varchar(10)

set @ID_inserted=0
set @Count_Table=0

DECLARE @column varchar(max), @Column_Name CURSOR
--Variable Delaration end

--Second Cursor start
declare @informationName varchar(50), @SysName varchar(50), @Var varchar(5)

set @informationName=@Database_Selected+'.'+'information_schema.COLUMNS'
Set @SysName=@Database_Selected+'.'+'sys.objects'
Set @Var='u'

--Database Selected start
Create Table #SearchResult(ID int identity(1,1) not null, TableName varchar(50), ColumnName varchar(50))
Create Table #SearchTestResult(ID int identity(1,1) not null, ColumnName varchar(max))
Create Table #Table_Name(ID int identity(1,1) not null, table_name varchar(50))

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Column_Nam')
drop table Column_Nam

insert into #Table_Name exec ('Select name from '+@SysName +' where type= '''+@Var+'''')

--First Cursor open
SET @Table_Name = CURSOR FOR Select table_name from #Table_Name

open @Table_Name

Fetch Next from @Table_Name into @Table

WHILE @@FETCH_STATUS = 0
BEGIN
set @Count_Table =@Count_Table+1
--Second cursor opened
--print 'Select column_name from '+@informationName +' where table_name= '''+@Table+''''s
Exec('SELECT column_name,data_type INTO Column_Nam FROM '+ @informationName +' where table_name = '''+@Table+'''')
SET @Column_Name = CURSOR FOR (select column_name from Column_Nam )

OPEN @Column_Name FETCH NEXT FROM @Column_Name INTO @column

set @Table=@Database_Selected+'.[dbo].['+@Table+']'
WHILE @@FETCH_STATUS = 0
BEGIN
set @data_type= (SELECT top 1 data_type FROM Column_Nam Where column_name= @column)

if @data_type is not null and (@data_type='varchar' or @data_type='nvarchar' or @data_type='Text')
begin
set @Result=null
if @column like '%-%' begin set @Result =('SELECT ''' + @column +''' FROM ' + @Table
+' Where ''' + @column + ''' Like '''+@keytosearch+'''') end
else
set @Result =('SELECT ' + @column +' FROM ' + @Table +' Where ' + @column + ' Like '''+@keytosearch+'''')

insert into #SearchTestResult exec(@Result)

set @ID=0
set @ID=(Select ID from #SearchTestResult where ID=Scope_identity())

if @ID is not null
begin
set @ID_inserted=(select top 1 ID from #SearchResult order by ID desc)
if @ID_inserted = @ID
begin
print ''
end
else
insert into #SearchResult values (@Table,@column)
end
end
FETCH NEXT FROM @Column_Name INTO @column
END
CLOSE @Column_Name
DEALLOCATE @Column_Name
--Second cursor closed

drop table Column_Nam

Fetch Next from @Table_Name into @Table
End

close @Table_Name

Deallocate @Table_Name
--First Cursor Closed

Select * from #SearchResult



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1442744
Posted Tuesday, April 16, 2013 7:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
Lowell (4/16/2013)
Sean Lange (4/16/2013)
[quote]jimoa312 (4/16/2013)

... DO NOT RUN THIS IN PRODUCTION!!!!!!!
...Run this ONLY ON A DEV/TEST server!!!!


One last thing...did I mention that you should not run this in production???

Happy hunting!!!


you should probably mention he shouldn't run this on production :)




One time somewhat recently when I posted this I suggested they don't run it in production. Then a day or so later I got a PM from the OP asking what to do because this thing was running and running and their production server was unable to process anything. Figured I would be a little over the top this time.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1442745
Posted Tuesday, April 16, 2013 8:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
Sean Lange (4/16/2013)
Lowell (4/16/2013)
Sean Lange (4/16/2013)
[quote]jimoa312 (4/16/2013)

... DO NOT RUN THIS IN PRODUCTION!!!!!!!
...Run this ONLY ON A DEV/TEST server!!!!


One last thing...did I mention that you should not run this in production???

Happy hunting!!!


you should probably mention he shouldn't run this on production :)




One time somewhat recently when I posted this I suggested they don't run it in production. Then a day or so later I got a PM from the OP asking what to do because this thing was running and running and their production server was unable to process anything. Figured I would be a little over the top this time.


Not over the top at all! This is one of those "learn from the experience of others" items they need to really be aware of.

Something like this needs to be mentioned a little more than once; You PM from another poster proves it; any MillionBillowRow tables will eat up all the CPU and diskspace in temp just to provide the results.

I've run this myself and seen it take hours when getting data to change the company web site from mycompany.net to mycompany.com.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1442752
Posted Tuesday, April 16, 2013 8:33 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 10:32 AM
Points: 546, Visits: 1,063
Soooooooooooo... you're saying this code is production ready?
Post #1442775
Posted Tuesday, April 16, 2013 8:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
Erin Ramsay (4/16/2013)
Soooooooooooo... you're saying this code is production ready?


You can do whatever you want on your last day.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1442778
Posted Wednesday, April 17, 2013 2:48 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 2,330, Visits: 3,509
The code's not really ready to be used in dev or QA either.

NO reason to search the same table multiple times, esp. not once per column.

Instead, should do a single access to each table for all relevant columns in one query.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1443498
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse