I’ve many times needed to find a value within an entire database.
Here’s a stored procedure that accomplishes that.
CREATE PROCEDURE [Developer].[FindMatchingValueInAnyTable]
(
@Value VARCHAR(64)
)
AS
BEGIN
DECLARE @sql VARCHAR(MAX)
DECLARE @TableName VARCHAR(64)
DECLARE @ColumnName VARCHAR(64)
CREATE TABLE #Results
(
TableName VARCHAR(64)
, ColumnName VARCHAR(64)
)
DECLARE TABLES CURSOR
FOR
SELECT
o.name
, c.name
FROM syscolumns c
INNER JOIN sysobjects o
ON c.id = o.id
WHERE o.type = ‘U’
AND c.xtype IN (167, 175, 231, 239)
ORDER BY
o.name
, c.name
OPEN TABLES
FETCH NEXT FROM TABLES
INTO @TableName
, @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘IF EXISTS(SELECT NULL FROM [‘ + @TableName + ‘] ‘
SET @sql = @sql + ‘WHERE RTRIM(LTRIM([‘ + @ColumnName + ‘])) LIKE ”%’ + @value + ‘%”) ‘
SET @sql = @sql + ‘INSERT INTO #Results ( TableName, ColumnName ) VALUES (”’ + @TableName + ”’, ”’
SET @sql = @sql + @ColumnName + ”’)’
EXEC(@sql)
FETCH NEXT FROM TABLES
INTO @TableName
, @ColumnName
END
CLOSE TABLES
DEALLOCATE TABLES
SELECT *
FROM #Results
DROP TABLE #Results
END