set nocount on

--Script parameters
declare	@Value as VARCHAR(50)
declare	@SearchStrings char(1)
declare	@SearchNumbers char(1)
declare	@SearchDates char(1)

----------------------------------------------------------------------------------------------------
--Script: ValueSearcher
--blindman, 9/19/2005
--Searches columns in user tables for a specified value.
--Returns the location where the value is found, and the number of records containing that value.
--Enter the value to be found in the Script parameter settings section below.
--Column types and comparison methods can be defined using the @Search parameters.
----------------------------------------------------------------------------------------------------
--Script parameter settings
set @Value = 'JEFF' --Enter the value to search for as a quoted string.
set @SearchStrings = 'L' --E=Exact string search, L=Search using Like operator, N=Do not search.
set @SearchNumbers = 'N' --Y=Search for numbers, N=Do not search.
set @SearchDates = 'N' --E=Exact datetime search, D=Search whole date parts only, N=Do not search.
----------------------------------------------------------------------------------------------------

--Processing variables
create table #Results (TableName sysname, ColumnName sysname, RecordCount bigint)
declare	@SQLString varchar(4000)

--check validity of parameters
if IsNumeric(@Value) = 0 set @SearchNumbers = 'N'
if IsDate(@Value) = 0 set @SearchDates = 'N'

--Create SQL statements to search the database
declare	SQLCursor cursor for
	--exact string columns
	select	'insert into #Results (TableName, ColumnName, RecordCount) select ''' + sysobjects.name + ''', ''' + syscolumns.name + ''', count(*) from ' + sysobjects.name + ' where ' + syscolumns.name + ' = ''' + @Value + ''' having count(*) > 0'
	from	sysobjects
		inner join syscolumns on sysobjects.id = syscolumns.id
		inner join systypes on syscolumns.xtype = systypes.xtype
	where	sysobjects.type = 'U'
		and systypes.name in ('char', 'nchar', 'nvarchar', 'sysname', 'uniqueidentifer', 'varchar')
		and @SearchStrings = 'E'
	UNION
	--like string columns
	select	'insert into #Results (TableName, ColumnName, RecordCount) select ''' + sysobjects.name + ''', ''' + syscolumns.name + ''', count(*) from ' + sysobjects.name + ' where ' + syscolumns.name + ' like ''%' + @Value + '%'' having count(*) > 0'
	from	sysobjects
		inner join syscolumns on sysobjects.id = syscolumns.id
		inner join systypes on syscolumns.xtype = systypes.xtype
	where	sysobjects.type = 'U'
		and systypes.name in ('char', 'nchar', 'nvarchar', 'sysname', 'uniqueidentifer', 'varchar')
		and @SearchStrings = 'L'
	UNION
	--numeric columns
	select	'insert into #Results (TableName, ColumnName, RecordCount) select ''' + sysobjects.name + ''', ''' + syscolumns.name + ''', count(*) from ' + sysobjects.name + ' where ' + syscolumns.name + ' = ' + @Value + ' having count(*) > 0'
	from	sysobjects
		inner join syscolumns on sysobjects.id = syscolumns.id
		inner join systypes on syscolumns.xtype = systypes.xtype
	where	sysobjects.type = 'U'
		and systypes.name in ('bigint', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smallint', 'smallmoney', 'tinyint')
		and @SearchNumbers = 'Y'
	UNION
	--Exact datetime columns
	select	'insert into #Results (TableName, ColumnName, RecordCount) select ''' + sysobjects.name + ''', ''' + syscolumns.name + ''', count(*) from ' + sysobjects.name + ' where ' + syscolumns.name + ' = ''' + @Value + ''' having count(*) > 0'
	from	sysobjects
		inner join syscolumns on sysobjects.id = syscolumns.id
		inner join systypes on syscolumns.xtype = systypes.xtype
	where	sysobjects.type = 'U'
		and systypes.name in ('datetime', 'smalldatetime')
		and @SearchDates = 'E'
	UNION
	--dateonly datetime columns
	select	'insert into #Results (TableName, ColumnName, RecordCount) select ''' + sysobjects.name + ''', ''' + syscolumns.name + ''', count(*) from ' + sysobjects.name + ' where convert(char(10), ' + syscolumns.name + ', 120) = convert(char(10), convert(datetime, ''' + @Value + '''), 120) having count(*) > 0'
	from	sysobjects
		inner join syscolumns on sysobjects.id = syscolumns.id
		inner join systypes on syscolumns.xtype = systypes.xtype
	where	sysobjects.type = 'U'
		and systypes.name in ('datetime', 'smalldatetime')
		and @SearchDates = 'D'

--Run the SQL Statements
Open	SQLCursor
Fetch next from SQLCursor into @SQLString
while @@FETCH_STATUS = 0
	begin
		exec (@SQLString)
		fetch next from SQLCursor into @SQLString
	end
Close	SQLCursor
Deallocate SQLCursor

--Display the results
select	cast(TableName + '.' + ColumnName as char(60)) Location,
	RecordCount
from	#Results
order by TableName,
	ColumnName

--Clean up
drop table #Results