DataBase GREP

SearchText.sql

/*
SELECT * 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu 
ON tc.CONSTRAINT_NAME = ccu.Constraint_name 
WHERE tc.CONSTRAINT_TYPE = 'Primary Key' 
*/


DECLARE @tableName sysname 
DECLARE @columnName sysname 
DECLARE @sqlCommand nvarchar(MAX) 
DECLARE @iIndex integer 
SET @iIndex = 0 

DECLARE col_cursor CURSOR FOR 
(
/*
	SELECT 
		 ccu.TABLE_NAME 
		,ccu.COLUMN_NAME 
		--,isc.DATA_TYPE
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
	
	LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu 
		ON tc.CONSTRAINT_NAME = ccu.Constraint_name
		
	LEFT JOIN INFORMATION_SCHEMA.COLUMNS isc 
		ON isc.TABLE_NAME = ccu.TABLE_NAME
		AND isc.COLUMN_NAME = ccu.COLUMN_NAME 
		
	WHERE (1=1) 
	--AND tc.CONSTRAINT_TYPE = 'Primary Key' 
	AND isc.DATA_TYPE = 'uniqueidentifier' 
*/
	
	SELECT 
		 ic.TABLE_NAME 
		,ic.COLUMN_NAME 
	FROM INFORMATION_SCHEMA.COLUMNS AS ic 
	
	INNER JOIN INFORMATION_SCHEMA.TABLES AS tbl 
		ON tbl.TABLE_NAME = ic.TABLE_NAME 
		AND tbl.TABLE_SCHEMA = ic.TABLE_SCHEMA 
		AND tbl.TABLE_CATALOG = ic.TABLE_CATALOG 
		AND TABLE_TYPE = 'BASE TABLE' -- No views
		
	WHERE (1=1) 
	--AND ic.TABLE_NAME LIKE 'T[_]%'
	AND 
	(
		ic.DATA_TYPE = 'varchar' 
		OR 
		ic.DATA_TYPE = 'nvarchar' 
		OR 
		ic.DATA_TYPE = 'text' 
		OR 
		ic.DATA_TYPE = 'ntext' 
		OR 
		ic.DATA_TYPE = 'char' 
		OR 
		ic.DATA_TYPE = 'nchar' 
	)
)

OPEN col_cursor    
	FETCH NEXT FROM col_cursor INTO @tableName, @columnName 
	WHILE @@FETCH_STATUS = 0 
	BEGIN 
		DECLARE @counts int
		DECLARE @strQueryText nvarchar(MAX) 
		SET @strQueryText = N'TextToSearch'
		
		--SET @sqlCommand = 'SELECT @cnt = COUNT(*) FROM [' + @tableName + N'] WHERE CAST([' + @columnName + N'] AS varchar(MAX)) = @strQueryText '
		--SET @sqlCommand = 'SELECT @cnt = COUNT(*) FROM [' + @tableName + N'] WHERE CAST([' + @columnName + N'] AS varchar(MAX)) = ''' + REPLACE(@strQueryText, N'''', N'''''') + N''' '
		SET @sqlCommand = 'SELECT @cnt = COUNT(*) FROM [' + @tableName + N'] WHERE CAST([' + @columnName + N'] AS nvarchar(MAX)) LIKE N''%' + REPLACE(@strQueryText, N'''', N'''''') + N'%'' '
		
		-- PRINT @sqlCommand
		EXECUTE sp_executesql @sqlCommand, N'@cnt int OUTPUT', @cnt=@counts OUTPUT 
		
		-- SELECT @counts AS Counts 
		IF @counts > 0
		BEGIN
			SET @sqlCommand = N'SELECT [' + @columnName + N'], ''' + @columnName + N''' AS columnName, ''' + @tableName + N''' AS tableName FROM [' + @tableName + N'] 
			WHERE CAST([' + @columnName + N'] AS varchar(MAX)) LIKE ''%' + REPLACE(@strQueryText, N'''', N'''''') + N'%'' ' 
			--WHERE CAST([' + @columnName + N'] AS varchar(MAX)) = ''' + REPLACE(@strQueryText, N'''', N'''''') + N''' ' 
			
			-- PRINT @sqlCommand 
			EXECUTE(@sqlCommand) 
		END
		
		
		SET @iIndex = @iIndex + 1 
		FETCH NEXT FROM col_cursor INTO @tableName, @columnName 
	END 
CLOSE col_cursor 
DEALLOCATE col_cursor