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
@MyStringAS nvarchar(99)= 'Phillip'

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

--Other data types
,@SearchGUIDAS smallint= 0--uniqueidentifier
,@SearchNumericAS smallint= 0--decimal, numeric, float, real, and money
,@SearchIntegerAS smallint= 0--bigint, int, smallint, tinyint

---------------------------------------------------------------------------------------------------
DECLARE
--Facts about @MyString
@MyStringLengthAS smallint--Length of @MyString
,@MyStringDecimalPointAS smallint--Location of decimal point in @MyString
,@MyStringPrecisionAS smallint--Precision of @MyString
,@MyStringScaleAS smallint--Scale of @MyString

,@MyStringIsGUIDAS smallint--Is @MyString a GUID?
,@MyStringIsNumericAS smallint--Is @MyString a number?
,@MyStringIsBigIntAS smallint--Is @MyString a big integer?
,@MyStringIsIntAS smallint--Is @MyString an integer?
,@MyStringIsSmallIntAS smallint--Is @MyString a small integer?
,@MyStringIsTinyIntAS smallint--Is @MyString a tiny integer?

--Facts for columns in cursor
,@SchemaNameAS nvarchar(128)
,@TableNameAS nvarchar(128)
,@ColumnNameAS nvarchar(128)
,@ColumnDataTypeAS nvarchar(128)
,@ColumnIDAS smallint
,@ColumnMaxLengthAS smallint
,@ColumnPrecisionAS smallint
,@ColumnScaleAS smallint

--Variables for dynamic sql
,@SQLCommandAS nvarchar(4000)--Outermost SQL command in dynamic string
,@SQLSubCommandAS nvarchar(1000)--Resultant command to be copied to new window for execution
,@MyStringWildCardAS nvarchar(100)--@MyString with wildcards added in front and back
,@MyStringSQLCommandAS nvarchar(100)--@MyString used in @SQLCommand
,@MyStringSQLSubCommandAS nvarchar(100)--@MyString used in @SQLSubCommand
,@OperatorAS nvarchar(10)--Character types can use ' LIKE ' while other types use ' = '. Be sure to include spaces.
,@SqlDataTypeAS nvarchar(128)

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

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

CREATE TABLE #Output
(
SchemaNamenvarchar(128)NULL
,TableNamenvarchar(128)NULL
,ColumnNamenvarchar(128)NULL
,ColumnIDintNULL
,SqlDataTypenvarchar(128)NULL
    ,SQLCommandnvarchar(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 = 1AND 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
---------------------------------------------------------------------------------------------------