String Search

/*-------------------------------------------------------------------------------------------------
Name: StringSearch.sql
Purpose: To search for a value in all applicable columns of a database.
Date: 2017-08-24
Author: Patrick Slesicki
Instructions:
	For SQL Server versions 2012 and later only. TRY_CONVERT function is used.
	Returns a SQL statement for all columns containing @MyString.
	Enclose the value for @MyString with tic marks ('), even if it is a number.
	Select the desired database, and run.
	It's best to choose a single data type option at a time.
---------------------------------------------------------------------------------------------------
--Preliminaries
-------------------------------------------------------------------------------------------------*/
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('tempdb.dbo.#Output') IS NOT NULL
	BEGIN
		DROP TABLE #Output
	END

---------------------------------------------------------------------------------------------------
--Declarations: User input required
---------------------------------------------------------------------------------------------------
DECLARE
	--String to search for
	@MyString				AS nvarchar(99)	= 'Phillip'

	--String search options
	,@SearchChar			AS smallint		= 1		--char, varchar, nchar, nvarchar, text, ntext
	,@SearchLOB				AS smallint		= 0		--varchar(MAX), nvarchar(MAX)
	,@SearchWildCard		AS smallint		= 0		--Adds wild cards '%' to the front and rear of @MyString for string searches only.

	--Other data types
	,@SearchGUID			AS smallint		= 0		--uniqueidentifier
	,@SearchNumeric			AS smallint		= 0		--decimal, numeric, float, real, and money
	,@SearchInteger			AS smallint		= 0		--bigint, int, smallint, tinyint

---------------------------------------------------------------------------------------------------
DECLARE
	--Facts about @MyString
	@MyStringLength			AS smallint				--Length of @MyString
	,@MyStringDecimalPoint	AS smallint				--Location of decimal point in @MyString
	,@MyStringPrecision		AS smallint				--Precision of @MyString
	,@MyStringScale			AS smallint				--Scale of @MyString

	,@MyStringIsGUID		AS smallint				--Is @MyString a GUID?
	,@MyStringIsNumeric		AS smallint				--Is @MyString a number?
	,@MyStringIsBigInt		AS smallint				--Is @MyString a big integer?	
	,@MyStringIsInt			AS smallint				--Is @MyString an integer?	
	,@MyStringIsSmallInt	AS smallint				--Is @MyString a small integer?	
	,@MyStringIsTinyInt		AS smallint				--Is @MyString a tiny integer?

	--Facts for columns in cursor
	,@SchemaName			AS nvarchar(128)
	,@TableName				AS nvarchar(128)
	,@ColumnName			AS nvarchar(128)
	,@ColumnDataType		AS nvarchar(128)
	,@ColumnID				AS smallint
	,@ColumnMaxLength		AS smallint
	,@ColumnPrecision		AS smallint
	,@ColumnScale			AS smallint

	--Variables for dynamic sql
	,@SQLCommand			AS nvarchar(4000)		--Outermost SQL command in dynamic string
	,@SQLSubCommand			AS nvarchar(1000)		--Resultant command to be copied to new window for execution
	,@MyStringWildCard		AS nvarchar(100)		--@MyString with wildcards added in front and back
	,@MyStringSQLCommand	AS nvarchar(100)		--@MyString used in @SQLCommand
	,@MyStringSQLSubCommand	AS nvarchar(100)		--@MyString used in @SQLSubCommand
	,@Operator				AS nvarchar(10)			--Character types can use ' LIKE ' while other types use ' = '. Be sure to include spaces.
	,@SqlDataType			AS nvarchar(128)

	--Punctuation
	,@cr					AS nchar(2)		= CHAR(13) + CHAR(10)	--Carriage return and line feed
	,@tic					AS nchar(1)		= CHAR(39)				--Single quote, aka tic mark or apostrophe

--Table variable and Temp tables
DECLARE @DataTypeTable		AS table(system_type_id tinyint NULL)

CREATE TABLE #Output
	(
	SchemaName				nvarchar(128)	NULL
	,TableName				nvarchar(128)	NULL
	,ColumnName				nvarchar(128)	NULL
	,ColumnID				int				NULL
	,SqlDataType			nvarchar(128)	NULL
    ,SQLCommand				nvarchar(4000)	NULL
	)

---------------------------------------------------------------------------------------------------
--Get facts about @MyString
---------------------------------------------------------------------------------------------------
SELECT
	@MyStringLength = LEN(@MyString)
	,@MyStringWildCard = CASE @SearchWildCard
		WHEN 1 THEN  '%' + @MyString + '%'
		ELSE @MyString
		END
	,@MyStringIsGUID = CASE
		WHEN TRY_CONVERT(uniqueidentifier, @MyString) IS NULL THEN 0
		ELSE 1
		END
	,@MyStringIsNumeric = ISNUMERIC(@MyString)

IF @MyStringIsNumeric = 1
	BEGIN
		--find decimal point position in @MyString
		SET @MyStringDecimalPoint = CHARINDEX('.', @MyString)
		
		--find numeric precision and scale for @MyString
		SELECT
			@MyStringPrecision = CASE @MyStringDecimalPoint
				WHEN 0 THEN @MyStringLength
				ELSE @MyStringLength - 1
				END
			,@MyStringScale = CASE @MyStringDecimalPoint
				WHEN 0 THEN 0
				ELSE @MyStringLength - @MyStringDecimalPoint
				END

		--find if @MyString is one of the following integer data types
		IF TRY_CONVERT(bigint, @MyString) IS NULL SET @MyStringIsBigInt = 0 ELSE SET @MyStringIsBigInt = 1
		IF TRY_CONVERT(int, @MyString) IS NULL SET @MyStringIsInt = 0 ELSE SET @MyStringIsInt = 1
		IF TRY_CONVERT(smallint, @MyString) IS NULL SET @MyStringIsSmallInt = 0 ELSE SET @MyStringIsSmallInt = 1
		IF TRY_CONVERT(tinyint, @MyString) IS NULL SET @MyStringIsTinyInt = 0 ELSE SET @MyStringIsTinyInt = 1
	END

---------------------------------------------------------------------------------------------------
--Data types to search
--Populate @DataTypeTable
---------------------------------------------------------------------------------------------------
--character types
IF @SearchChar = 1
	BEGIN
		INSERT INTO @DataTypeTable(system_type_id)
		SELECT system_type_id
		FROM sys.types 
		WHERE name IN('char','varchar','nchar','nvarchar','text','ntext')
	END

--lob types
--insert types if @SearchChar = 0
IF @SearchLOB = 1
	BEGIN
		IF NOT EXISTS
			(
			SELECT * 
			FROM @DataTypeTable AS dtt 
			JOIN sys.types AS ty ON dtt.system_type_id = ty.system_type_id
			WHERE ty.name IN('varchar','nvarchar')
			)
			BEGIN
				INSERT INTO @DataTypeTable(system_type_id)
				SELECT system_type_id
				FROM sys.types 
				WHERE name IN('varchar','nvarchar')
			END
	END

--guid
IF @SearchGUID = 1 AND @MyStringIsGUID = 1
	BEGIN
		INSERT INTO @DataTypeTable(system_type_id)
		SELECT system_type_id
		FROM sys.types 
		WHERE name = 'uniqueidentifier'
	END

--decimal, numeric, float, real, money
IF @SearchNumeric = 1 AND @MyStringIsNumeric = 1
	BEGIN
		INSERT INTO @DataTypeTable(system_type_id)
		SELECT system_type_id
		FROM sys.types 
		WHERE name IN('decimal','numeric','float','real','money')
	END

--integer types
IF @SearchInteger = 1 AND @MyStringIsTinyInt = 1
	BEGIN
		INSERT INTO @DataTypeTable(system_type_id)
		SELECT system_type_id
		FROM sys.types 
		WHERE name IN('tinyint','smallint','int','bigint')
	END
ELSE IF @SearchInteger = 1 AND @MyStringIsSmallInt = 1
	BEGIN
		INSERT INTO @DataTypeTable(system_type_id)
		SELECT system_type_id
		FROM sys.types 
		WHERE name IN('smallint','int','bigint')
	END
ELSE IF @SearchInteger = 1 AND @MyStringIsInt = 1
	BEGIN
		INSERT INTO @DataTypeTable(system_type_id)
		SELECT system_type_id
		FROM sys.types 
		WHERE name IN('int','bigint')
	END
ELSE IF @SearchInteger = 1 AND @MyStringIsBigInt = 1
	BEGIN
		INSERT INTO @DataTypeTable(system_type_id)
		SELECT system_type_id
		FROM sys.types 
		WHERE name = 'bigint'
	END

---------------------------------------------------------------------------------------------------
--Define cursor
--Aside from datatypes, all filtering and exclusions are done in the WHERE clause of this SELECT statement.
---------------------------------------------------------------------------------------------------
DECLARE TableCursor CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR

SELECT
	SCHEMA_NAME(t.schema_id)
	,t.name
	,c.name
	,c.column_id
	,ty.name
	,c.max_length
	,c.precision
	,c.scale
FROM sys.tables AS t
JOIN sys.columns AS c
	ON t.object_id = c.object_id
JOIN @DataTypeTable AS dtt
	ON c.system_type_id = dtt.system_type_id
JOIN sys.types AS ty
	ON dtt.system_type_id = ty.user_type_id	--Note the switch from system_type_id to user_type_id
WHERE
	--Exclude system schemas
	SCHEMA_NAME(t.schema_id) NOT IN('cdc','sys')

	--Exclude system tables
	AND t.name NOT LIKE 'ms%'
	AND t.name NOT LIKE 'sys%'

	--Exclude tables with no records
	AND	(
		SELECT SUM(rows) 
		FROM sys.partitions
		WHERE
			object_id = t.object_id
			AND index_id < 2
		) > 0

	--Include/Exclude LOB types based on @SearchLOB selection
	--LOB types have a max_length of -1
	AND c.max_length >= -(@SearchLOB)

	--Exclude columns for char types taking into account 
	--	1. @SearchChar and @SearchLob are distinct searches
	--	2. max length of max_length is 8000
	AND c.max_length NOT BETWEEN 0 AND 
		(
		CASE 
			WHEN ty.name IN('char','varchar') AND @SearchChar = 1 AND c.max_length > 0
				THEN c.max_length - 1
			WHEN ty.name IN('nchar','nvarchar') AND @SearchChar = 1	AND c.max_length > 0
				THEN c.max_length / 2 - 1
			WHEN ty.name IN('varchar','nvarchar') AND @SearchChar = 0 AND @SearchLob = 1
				THEN 8000
			ELSE 0
			END
		)

	--Exclude columns where precision is smaller than @MyString's precision for decimal/numeric searches
	AND c.precision  >=
		(
		CASE
			WHEN ty.name IN('decimal','numeric','money') THEN @MyStringPrecision - @MyStringScale + c.scale
			ELSE 0
			END
		)

	--Exclude columns where scale is smaller than @MyString's scale for decimal/numeric searches
	AND c.scale >=
		(
		CASE
			WHEN ty.name IN('decimal','numeric','money') THEN @MyStringScale
			ELSE 0
			END
		)

---------------------------------------------------------------------------------------------------
--Open cursor and get records
---------------------------------------------------------------------------------------------------
OPEN TableCursor

FETCH NEXT 
FROM TableCursor 
INTO
	@SchemaName
	,@TableName
	,@ColumnName
	,@ColumnID
	,@ColumnDataType
	,@ColumnMaxLength
	,@ColumnPrecision
	,@ColumnScale

---------------------------------------------------------------------------------------------------
--Loop through cursor
---------------------------------------------------------------------------------------------------
WHILE @@FETCH_STATUS = 0
	BEGIN
		-------------------------------------------------------------------------------------------
		--Assign values to the following @SQLCommand components
		--	@MyStringSQLCommand
		--	@MyStringSQLSubCommand
		--	@Operator 
		-------------------------------------------------------------------------------------------
		--character type exact match
		IF @ColumnDataType IN('char','varchar','nchar','nvarchar') 
			AND @SearchWildCard = 0
			BEGIN
				SELECT
					@MyStringSQLCommand = + @tic + @MyString + @tic
					,@MyStringSQLSubCommand = + @tic + @MyString + @tic
					,@Operator = ' = '
			END
		--character type fuzzy match
		ELSE IF @ColumnDataType IN('char','varchar','nchar','nvarchar') 
			AND @SearchWildCard = 1
			BEGIN
				SELECT
					@MyStringSQLCommand = + @tic + @MyStringWildCard + @tic
					,@MyStringSQLSubCommand = + @tic + @MyStringWildCard + @tic
					,@Operator = ' LIKE '			END
		ELSE IF @ColumnDataType IN('text','ntext') 
			BEGIN
				SELECT
					@MyStringSQLCommand = + @tic + @MyString + @tic
					,@MyStringSQLSubCommand = + @tic + @MyString + @tic
					,@Operator = ' LIKE '
			END
		ELSE IF @ColumnDataType = 'uniqueidentifier'
			BEGIN
				SELECT
					@MyStringSQLCommand = + @tic + @MyString + @tic
					,@MyStringSQLSubCommand = + @tic + @MyString + @tic
					,@Operator = ' = '
			END
		ELSE IF @ColumnDataType IN('decimal','numeric')
			BEGIN
				SELECT
					@MyStringSQLCommand = 'CONVERT(' + @ColumnDataType + '(' + CONVERT(nvarchar(39), @ColumnPrecision) + ',' + CONVERT(nvarchar(39), @ColumnScale) + '),' +  @MyString + ')'
					,@MyStringSQLSubCommand = @MyString
					,@Operator = ' = '
			END
		ELSE IF @ColumnDataType IN('float','real','money','bigint','int','smallint','tinyint')
			BEGIN
				SELECT
					@MyStringSQLCommand = 'CONVERT(' + @ColumnDataType + ',' + @MyString + ')'
					,@MyStringSQLSubCommand = @MyString
					,@Operator = ' = '
			END

		-------------------------------------------------------------------------------------------
		--sql data type
		-------------------------------------------------------------------------------------------
		SET @SqlDataType = 
			@ColumnDataType
			+ CASE
				WHEN @ColumnMaxLength = -1 THEN '(MAX)' 
				WHEN @ColumnMaxLength > 0 AND @ColumnDataType IN('char','varchar') THEN '(' + CONVERT(nvarchar(5), @ColumnMaxLength) + ')'
				WHEN @ColumnMaxLength > 0 AND @ColumnDataType IN('nchar','nvarchar') THEN '(' + CONVERT(nvarchar(5), @ColumnMaxLength / 2) + ')'
				WHEN @ColumnDataType IN('decimal','numeric') THEN '(' + CONVERT(nvarchar(20), @ColumnPrecision) + ', ' + CONVERT(nvarchar(20), @ColumnScale) + ')'
				ELSE ''
				END

		-------------------------------------------------------------------------------------------
		--Sub Command
		-------------------------------------------------------------------------------------------
		SET @SQLSubCommand = 'SELECT ' + QUOTENAME(@ColumnName) + ', * FROM ' + QUOTENAME(DB_NAME()) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@ColumnName) + @Operator + @MyStringSQLSubCommand + '' 
		
		-------------------------------------------------------------------------------------------
		--Main command
		-------------------------------------------------------------------------------------------
		SET @SQLCommand = 
			'SET NOCOUNT ON'
			+ @cr + 'IF EXISTS (SELECT * FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@ColumnName) + @Operator + @MyStringSQLCommand + ')' 
			+ @cr + 'BEGIN'
			+ @cr + 'INSERT INTO #Output(SchemaName, TableName, ColumnName, ColumnID, SqlDataType, SQLCommand)' 
			+ @cr + 'SELECT ' + QUOTENAME(@SchemaName, @tic + @tic) + ', ' + QUOTENAME(@TableName, @tic + @tic) + ', ' + QUOTENAME(@ColumnName, @tic + @tic) + ', ' + CONVERT(nvarchar(15), @ColumnID) + ', ' + QUOTENAME(@SqlDataType, @tic + @tic) + ', ' + QUOTENAME(@SQLSubCommand, @tic + @tic)
			+ @cr + 'PRINT ' + @tic + 'RECORDS FOUND IN ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@ColumnName) + @tic
			+ @cr + 'END'
			+ @cr + @cr
		
		-------------------------------------------------------------------------------------------
		--Execute dynamic sql command
		-------------------------------------------------------------------------------------------
		PRINT 'Checking ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@ColumnName)
		EXECUTE dbo.sp_executesql @stmt = @SQLCommand

		-------------------------------------------------------------------------------------------
		--Get next record in cursor
		-------------------------------------------------------------------------------------------
		FETCH NEXT
		FROM TableCursor
		INTO
			@SchemaName
			,@TableName
			,@ColumnName
			,@ColumnID
			,@ColumnDataType
			,@ColumnMaxLength
			,@ColumnPrecision
			,@ColumnScale
	END

PRINT 'Finished checking database ' + QUOTENAME(DB_NAME())

---------------------------------------------------------------------------------------------------
--Get results
---------------------------------------------------------------------------------------------------
SELECT
	SchemaName
	,TableName
	,ColumnName
	,ColumnID
	,SqlDataType
	,SQLCommand
FROM #Output
ORDER BY
	SchemaName
	,TableName
	,ColumnName

---------------------------------------------------------------------------------------------------
--Clean up
---------------------------------------------------------------------------------------------------
CLOSE TableCursor
DEALLOCATE TableCursor
DROP TABLE #Output

---------------------------------------------------------------------------------------------------
--END
---------------------------------------------------------------------------------------------------