declare @DataToFind varchar(100)
set @DataToFind = 'xxx'
--'CBDEP00004537'
--'dep4564'
DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)
DECLARE @ISDATE 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_Name LIKE 'CB%'
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE Table_Type = 'BASE TABLE'
And (Data_Type = 'char' OR Data_Type = 'varchar')
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 = 'If Exists(Select *
From ReplaceTableName
Where [ReplaceColumnName]
like ''%' + CONVERT(VARCHAR(30), @DataToFind, 126) + '%''
)
Set @DataExists = 1
Else
Set @DataExists = 0',
@PARAMETERS = '@DataExists Bit OUTPUT',
@i = 1
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, ' SELECT * FROM ' + TableName + ' WHERE ' + ColumnName + ' = ''' + @DataToFind + '''' AS 'Query String'
FROM @Temp
WHERE DataFound = 1
GO
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,326 Super User 2024 Season 2
Martin Dráb 228,166 Most Valuable Professional
nmaenpaa 101,148