# I wanted to find ALL references to a particular string (domain\user) and I had no idea WHERE it might be.
# so how do you ask SQL to go look everywhere and report back?
# like this...
DECLARE @searchValue varchar(100)
SET @searchValue = 'domain\username'
SELECT 'select ''' +
'.' + TABLE_NAME +
'.' + COLUMN_NAME +
''' from ' + TABLE_SCHEMA + '.' + table_name +
' where ' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + column_Name + ' = ''' + @searchvalue + ''' union '
FROM information_schema.columns WHERE data_type in ('nvarchar','varchar' )
- Open SQL Management studio
- Open a new query window
- Make sure you are connected to the right database
- Hit Cntrl-T (we want text output)
- Run the query above against your database, replace @searchValue
- A big fat query should be in the results pane below. Copy and paste it into a new window.
- Strip out the last 'union' statement
- now the new query is ready so - run...