/*-------------------------------------------------------------------------------------------------
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
---------------------------------------------------------------------------------------------------