SQL Server 入門 SQL Server 入門

ホーム > 便利なT-SQL&クエリー集 > 全テーブル・全カラムの値を文字列検索する

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

データベース内の全テーブル・全カラムの値を文字列検索する

データベース内で、全てのテーブルの全カラムを対象に、特定の文字が使われていないか調べなくてはいけないような時ないでしょうか?

作る機会があったので、そんな時に使えるスクリプトをここに残しておきます。


データベース内の全テーブル・全カラムの値を文字列検索するスクリプト

こちらが、データベース内の全テーブル・全カラムの値を文字列検索するスクリプトです。

-------------------------------------
-- @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;

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

検索対象のカラムのデータタイプは char, varchar, text, nchar, nvarchar, ntext, xml としています。

結果をさらにフィルターしたいこともあるかと思ったので、DROP TABLE はコメントアウトしてあります。

SQL Server 関連の人気書籍
ホーム > 便利なT-SQL&クエリー集 > 全テーブル・全カラムの値を文字列検索する