USE MASTER
GO

/* Regular Expression Function
**
** uses call to vbscript.RegExp
**
** Charlie (22/Jul/2008)
*/

IF OBJECT_Id('dbo.fn_regex_CS') IS NOT NULL DROP FUNCTION dbo.fn_regex_CS
GO

CREATE FUNCTION dbo.fn_regex_CS(
	@pattern VARCHAR(255)
	, @matchstring TEXT
	)
	RETURNS INT

AS BEGIN
	DECLARE @obj INT                   SET @obj = -1
	DECLARE @res INT                   SET @res = -1
	DECLARE @match BIT                 SET @match = 0

	-- Make the @res Object.
	EXEC @res = sp_OACreate 'VBScript.RegExp', @obj OUT
	IF (@res <> 0) RETURN -1

	-- Assign the Pattern to it.
	EXEC @res = sp_OASetProperty @obj, 'Pattern', @pattern
	IF (@res <> 0) RETURN -2

	-- Set to ignore Case
	EXEC @res = sp_OASetProperty @obj, 'IgnoreCase', 1
	IF (@res <> 0) RETURN -3

	-- Execute the regular expression
	EXEC @res = sp_OAMethod @obj, 'Test',@match OUT, @matchstring
	IF (@res <> 0) RETURN -4

	-- Cleanup the object
	EXEC @res = sp_OADestroy @obj

	-- Return the results
	RETURN @match
END
GO


/* Search across All Text columns for a pattern
**
** Charlie (21/Jul/2008), Modified (22/Jul/2008)
*/

-- ==== Configuration =========================================================

DECLARE @searchString NVARCHAR(255)    SET @searchString = '<[^>]*?(>|$)'
DECLARE @databaseMask NVARCHAR(255)    SET @databaseMask = '<YOUR_DATABASE_NAME_HERE>'
DECLARE @debug BIT                     SET @debug = 0
DECLARE @loggingLevel INT              SET @loggingLevel = 0

-- ==== ============= =========================================================

SET NOCOUNT ON
IF object_Id('tempDb..#found') IS NOT NULL DROP TABLE #found
IF object_Id('tempDb..#searchList') IS NOT NULL DROP TABLE #searchList

-- Storage Objects and Variables
CREATE TABLE #found (
	[databaseName] NVARCHAR(255)
	, [tableName] NVARCHAR(255)
	, [columnName] NVARCHAR(255)
	, [Id] NVARCHAR(255)
	, [value] TEXT
	)

CREATE TABLE #searchList (
	[TABLE_CATALOG] NVARCHAR(255)
	, [TABLE_SCHEMA] NVARCHAR(255)
	, [TABLE_NAME] NVARCHAR(255)
	, [COLUMN_NAME] NVARCHAR(255)
	, [COLLATION_NAME] NVARCHAR(255)
	, [DATA_TYPE] NVARCHAR(255)
	, [ID_FIELD] BIT DEFAULT 0
	)

DECLARE @sql NVARCHAR(4000)
DECLARE @database NVARCHAR(255)
DECLARE @tableName NVARCHAR(255)
DECLARE @columnName NVARCHAR(255)
DECLARE @schema NVARCHAR(255)
DECLARE @dataType NVARCHAR(255)
DECLARE @collation NVARCHAR(255)
DECLARE @idField BIT
DECLARE @msgText VARCHAR(300)
DECLARE @now DATETIME

-- This goes through each database and builds the searchlist table
DECLARE databaseCursor CURSOR FOR
	SELECT
		[name]
	FROM
		[master].[dbo].[sysDatabases]
	WHERE
		[name] LIKE @databaseMask

OPEN databaseCursor
	FETCH NEXT FROM databaseCursor INTO @database

	WHILE ( @@FETCH_STATUS = 0 ) BEGIN

		SET @sql = N'
		INSERT INTO #searchList (
			[TABLE_CATALOG]
			, [TABLE_SCHEMA]
			, [TABLE_NAME]
			, [COLUMN_NAME]
			, [COLLATION_NAME]
			, [DATA_TYPE]
			)
		SELECT
			''['' + [TABLE_CATALOG] + '']''
			, ''['' + [TABLE_SCHEMA] + '']''
			, ''['' + [TABLE_NAME] + '']''
			, ''['' + [COLUMN_NAME] + '']''
			, [COLLATION_NAME]
			, [DATA_TYPE]
		FROM
			' + @database + '.information_schema.columns
		WHERE
			[DATA_TYPE] IN (
				''VARCHAR''
				, ''TEXT''
				, ''CHAR''
				, ''NVARCHAR''
				, ''NCHAR''
				, ''NTEXT''

		UPDATE sl
		SET
			[ID_FIELD] = 1
		FROM
			#searchList sl
			JOIN ' + @database + '.information_schema.columns isc ON
				''['' + isc.[TABLE_NAME] + '']'' = sl.[TABLE_NAME]
				AND isc.[COLUMN_NAME] = ''Id'''

		IF @debug = 1 PRINT @sql
		EXEC (@sql)

		FETCH NEXT FROM databaseCursor INTO @database
	END
CLOSE databaseCursor
DEALLOCATE databaseCursor

-- Now have to search through the list. This will take a while...
DECLARE searchCursor CURSOR LOCAL READ_ONLY FOR
SELECT
	[TABLE_CATALOG]
	, [TABLE_SCHEMA]
	, [TABLE_NAME]
	, [COLUMN_NAME]
	, [DATA_TYPE]
	, [COLLATION_NAME]
	, [ID_FIELD]
FROM
	#searchList
ORDER BY
	[TABLE_CATALOG]
	, [TABLE_NAME]
	, [COLUMN_NAME]

OPEN searchCursor

	FETCH NEXT FROM searchCursor INTO
		@database
		, @schema
		, @tableName
		, @columnName
		, @dataType
		, @collation
		, @IdField

	WHILE ( @@FETCH_STATUS = 0 ) BEGIN

		IF @loggingLevel > 1 BEGIN
			SET @now = GETDATE()
			SET @msgText =
				CONVERT(CHAR(11), @now, 106) + ' @ '
				+ CONVERT(CHAR(8), @now, 108)
				+ ' - Checking.... '
				+ @database +
				'.'
				+ @schema
				+
				'.'
				+ @tableName
				+ '.'
				+ @columnName

			RAISERROR(@msgText, 0 , 1) WITH NOWAIT
		END
			
		SET @sql = N'
		INSERT INTO #found (
			[databaseName]
			, [tableName]
			, [columnName]
			, [Id]
			, [value]
			)
		SELECT
			@database
			, @tableName
			, @columnName
			, ' + CASE @IdField WHEN 1 THEN '[Id]' ELSE 'NULL' END + '
			, ' + @columnName + '
		FROM
			' + @database + '.' + @schema + '.' + @tableName + ' WITH (NOLOCK)
		WHERE
			master.dbo.fn_regex_CS(@searchString, ' + @columnName + ') <> 0'

		-- Show and execute the sql
		IF @debug = 1 PRINT @sql

		EXEC sp_executeSql
			@sql
			, N'@database NVARCHAR(255)
			, @tableName NVARCHAR(255)
			, @columnName NVARCHAR(255)
			, @searchString NVARCHAR(255)'
			, @database
			, @tableName
			, @columnName
			, @searchString

		FETCH NEXT FROM searchCursor INTO
			@database
			, @schema
			, @tableName
			, @columnName
			, @dataType
			, @collation
			, @IdField

	END

CLOSE searchCursor
DEALLOCATE searchCursor

SELECT * FROM #found FOR XML RAW

quote:

---

Originally posted by Mindjogger

Hello Charlie,

thank you for this. It loooks really complex for me. Without any help it will take alooong time for me to understand.

As I am not very familar with SQL I am a little bit nervus about the drop commansds ;-)

IF object_Id('tempDb..#found') IS NOT NULL DROP TABLE #found
IF object_Id('tempDb..#searchList') IS NOT NULL DROP TABLE #searchList



Thank you.

B.S.

---