Just run this in a database.
As long as its not a text / ntext field..
SET NOCOUNT ON /* AUTHOR DT 10/19/15 Server: Microsoft SQL Server */
CREATE TABLE #TBL_SEARCH (server_name varchar(100), db_name varchar(100), table_name varchar(40), raw_tablename varchar(max), [schema_nm] varchar(max), matching_field varchar(40), counts int,
A_SAMPLE varchar(100)
)
DECLARE Table_Column_Matches CURSOR
READ_ONLY
FOR (
select '[' + OBJECT_SCHEMA_NAME(o.id) + '].[' + o.name + ']' as table_name, o.name as raw_table_name,
OBJECT_SCHEMA_NAME(o.id) as [schema_nm], c.name as field_name, t.name as datatype_field from sysobjects o
join syscolumns c
on o.id=c.id
join systypes t
on t.type=c.type and t.xtype=c.xtype and c.xusertype=t.xusertype
where
o.type='U' and
t.name IN ('char', 'nchar','nvarchar','varchar' )
)
DECLARE @table_name varchar(300)
DECLARE @field_name varchar(300)
DECLARE @raw_table_name varchar(300)
DECLARE @schema_nm varchar(300)
DECLARE @datatype_field varchar(300)
declare @query nvarchar(3000)
OPEN Table_Column_Matches
FETCH NEXT FROM Table_Column_Matches INTO @table_name, @raw_table_name, @schema_nm, @field_name, @datatype_field
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
set @query = 'SELECT @@SERVERNAME, DB_NAME(), ''' + @table_name + ''' as [table_name], ''[' + @raw_table_name + ']'' as raw_table_name,''[' + @schema_nm + ']'' as schema_nm, '''+ @field_name +''' as matching_field,
COUNT([' + @field_name + ']) AS COUNTS,
MAX([' + @field_name + '] ) A_SAMPLE
FROM ' + @table_name + ' WITH (NOLOCK)
WHERE UPPER([' + @field_name + ']) LIKE ''IT DEPT%''
'
INSERT INTO #TBL_SEARCH
exec sp_executesql @query
END
FETCH NEXT FROM Table_Column_Matches INTO @table_name, @raw_table_name, @schema_nm, @field_name, @datatype_field
END
CLOSE Table_Column_Matches
DEALLOCATE Table_Column_Matches
select * from #TBL_SEARCH
where counts > 0
drop table #TBL_SEARCH