Search a value from the whole database
Query help you find things within a sql database
If you ever want to search something from the database but you don't know which tables to search from, here is the query you need:
/****** Script for SelectTopNRows command from SSMS ******/
SET NOCOUNT ON
DECLARE
@TotalRows int,
@Counter int,
@TableName varchar(50),
@ColumnName varchar(50),
@FieldValue varchar(250),
@SQLCommand nvarchar(1000),
@ValueToFind varchar(100)
SET @ValueToFind = 'VAULE TO SEARCH'
DECLARE @MyTable table
( RowID int IDENTITY,
TableName varchar(50),
ColumnName varchar(50)
)
CREATE TABLE #FoundTable
( RowID int IDENTITY,
Tablename varchar(50)
)
INSERT INTO @MyTable
SELECT
TABLE_NAME,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ( 'char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext' )
SELECT
@TotalRows = @@ROWCOUNT,
@Counter = 1
WHILE ( @Counter <= @TotalRows )
BEGIN
SELECT
@TableName = TableName,
@ColumnName = ColumnName
FROM @MyTable
WHERE RowID = @Counter
SET @SQLCommand = 'IF EXISTS ( ' +
'SELECT 1 FROM [' + @TableName + '] WHERE [' + @ColumnName + '] LIKE ''%' + @ValueToFind + '%'' ' +
' )' +
'INSERT INTO #FoundTable ' +
' SELECT ''' + @TableName + '(' + @ColumnName + ')'''
EXECUTE sp_executesql @SQLCommand
SET @Counter = ( @Counter + 1 )
END
SELECT * FROM #FoundTable
DROP TABLE #FoundTable