Find the String in all the Tables in a Database for Exact Match or Non Exact Match

SP for find the String in all the Tables in a Database for Exact Match or Non Exact Match

The below given SP will give u the schema name,table name and column name. By which you can find your string.
For Example : - SchemaName TableName ColumnName
dbo employee empname

From this you easily find which table's column have a string that you are finding.


--FindMyData_String 'ram',1

alter PROCEDURE FindMyData_String
@DataToFind NVARCHAR(4000),
@ExactMatch BIT = 0
AS
SET NOCOUNT ON

DECLARE @Temp TABLE
( RowId INT IDENTITY(1,1),
SchemaName sysname,
TableName sysname,
ColumnName SysName,
DataType VARCHAR(100),
DataFound BIT
)

INSERT INTO @Temp
( TableName,
SchemaName,
ColumnName,
DataType
)
SELECT C.Table_Name,
C.TABLE_SCHEMA,
C.Column_Name,
C.Data_Type
FROM Information_Schema.Columns AS C
INNER Join Information_Schema.Tables AS T
ON C.Table_Name = T.Table_Name
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE Table_Type = 'Base Table'
And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')

--SELECT * FROM @Temp
--RETURN


DECLARE @i INT
DECLARE @MAX INT
--DECLARE @TableName sysname
--DECLARE @ColumnName sysname
--DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)

SELECT @SQLTemplate = CASE WHEN @ExactMatch = 1
THEN 'If Exists(Select *
From ReplaceTableName
Where Convert(nVarChar(4000), [ReplaceColumnName])
= ''' + @DataToFind + '''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
ELSE 'If Exists(Select *
From ReplaceTableName
Where Convert(nVarChar(4000), [ReplaceColumnName])
Like ''%' + @DataToFind + '%''
)
Set @DataExists = 1
Else
Set @DataExists = 0'
END,

@PARAMETERS = '@DataExists Bit OUTPUT',
@i = 1

--PRINT @SQLTemplate
--SELECT * FROM @Temp
--RETURN


SELECT @i = 1, @MAX = MAX(RowId)
FROM @Temp

WHILE @i <= @MAX
BEGIN
SELECT @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
FROM @Temp
WHERE RowId = @i


--PRINT @SQL
EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

IF @DataExists =1
UPDATE @Temp SET DataFound = 1 WHERE RowId = @i

SET @i = @i + 1
END

SELECT SchemaName,TableName, ColumnName
FROM @Temp
WHERE DataFound = 1
GO