全テーブル・全カラムの値を文字列検索する

-------------------------------------
-- @TextToFind に検索したい文字列を入れてください。
-------------------------------------
DECLARE @TextToFind NVARCHAR(100) = N'design';
--DECLARE @TextToFind NVARCHAR(100) = N'Women''s';
--DECLARE @TextToFind NVARCHAR(100) = N'女';
--DECLARE @TextToFind NVARCHAR(100) = N'0%';

-------------------------------------
-- LIKE operator で使う準備
-------------------------------------
DECLARE @LIKE_TextToFind NVARCHAR(100),
	@EscapeChar CHAR(1) = '\';

SET @LIKE_TextToFind = REPLACE(@TextToFind, '''','''''');
SET @LIKE_TextToFind = REPLACE(@LIKE_TextToFind, @EscapeChar, @EscapeChar + @EscapeChar);

-- ワイルドカードとして使いたい場合はコメントアウトしてください
SET @LIKE_TextToFind = REPLACE(@LIKE_TextToFind, '%', @EscapeChar + '%');
SET @LIKE_TextToFind = REPLACE(@LIKE_TextToFind, '_', @EscapeChar + '_');
SET @LIKE_TextToFind = REPLACE(@LIKE_TextToFind, '[', @EscapeChar + '[');

SET @LIKE_TextToFind = '%' + @LIKE_TextToFind + '%';

-------------------------------------
-- 検索
-------------------------------------
IF OBJECT_ID(N'tempdb..#SearchResult', N'U') IS NOT NULL
DROP TABLE #SearchResult; 

CREATE TABLE #SearchResult (
	SchemaName	NVARCHAR(128),
	TableName	NVARCHAR(128),
	ColumnName	NVARCHAR(128),
	ColumnValue	NVARCHAR(MAX)
);


DECLARE @SchemaName	NVARCHAR(128),
	@TableName	NVARCHAR(128),
	@ColumnName	NVARCHAR(128),
	@Sql		NVARCHAR(MAX);

DECLARE crSearch CURSOR 
	LOCAL FAST_FORWARD
FOR
	SELECT		s.name,
			T.name,
			C.name
	FROM		sys.schemas AS S
				INNER JOIN sys.tables AS T
					ON S.schema_id = T.schema_id		
				INNER JOIN sys.columns AS C
					ON T.object_id = C.object_id
				INNER JOIN  sys.types AS TP
					ON C.user_type_id = TP.user_type_id
	WHERE		TP.name IN ('char','varchar','text','nchar','nvarchar','ntext','xml')
	ORDER BY	s.name,
			T.name,
			C.name;

OPEN crSearch;

FETCH NEXT FROM crSearch
INTO	@SchemaName,
	@TableName,
	@ColumnName;

WHILE @@FETCH_STATUS = 0
BEGIN

	SET @Sql = 'SELECT ''' + @SchemaName + ''', ''' + @TableName + ''', ''' + @ColumnName + ''', CAST([' + @ColumnName + '] AS NVARCHAR(MAX)) '
				+ ' FROM [' + @SchemaName + '].[' + @TableName + '] WITH (NOLOCK)'
				+ ' WHERE CAST([' + @ColumnName + '] AS NVARCHAR(MAX)) LIKE N''' + @LIKE_TextToFind + ''' ESCAPE ''' + @EscapeChar + '''';

	INSERT INTO #SearchResult
		EXEC sp_executesql @Sql;

	FETCH NEXT FROM crSearch
	INTO	@SchemaName,
		@TableName,
		@ColumnName;

END

CLOSE crSearch;
DEALLOCATE crSearch;

-------------------------------------
-- 結果の表示
-------------------------------------
SELECT		*
FROM		#SearchResult
ORDER BY	SchemaName,
		TableName,
		ColumnName;

--DROP TABLE #SearchResult;