Search SQL database for every table and field that contains a certain number

Overview:

Trying to research how a SQL Server database stores data? This SQL may help. If you execute this SQL in SQL Server Management Studio, this SQL will return every table and field in a database that contains the number you specify on line 9 of this code (see the line that says Set @search_str = '1111').

To use:

Just edit line 9 to put in your search term (replace the "1111") and then execute it.

SQL CODE:

DECLARE

@VAR1 SYSNAME,

@VAR2 SYSNAME,

@VAR3 NVARCHAR(4000),

@search_str NVARCHAR(1000),

@counter INT,

@dbname SYSNAME

-- SET SEARCH CONDITION HERE--

SET @search_str='1111'

IF EXISTS (SELECT 1 FROM TEMPDB..SYSOBJECTS WHERE NAME LIKE '#FTS_TABLE%')

DROP TABLE #FTS_TABLE

CREATE TABLE #FTS_TABLE (TABLE_NAME VARCHAR(150), CONTENT VARCHAR(1000),

CONTAIN VARCHAR(15), SEARCH_STRING VARCHAR(1000))

SET @counter=0

SELECT @dbname= db_name()

DECLARE CUR CURSOR FOR

SELECT SYSOBJECTS.NAME , SYSCOLUMNS.NAME FROM SYSOBJECTS

INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID = SYSCOLUMNS.ID

INNER JOIN SYSTYPES ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE WHERE

SYSOBJECTS.TYPE ='U' AND

OBJECTPROPERTY(OBJECT_ID(SYSOBJECTS.NAME), N'ISMSSHIPPED') = 0 AND

SYSTYPES.NAME IN('numeric', 'uniqueidentifier', 'int', 'tinyint', 'money')--AND

--SYSOBJECTS.NAME LIKE '%MAM_%'

ORDER BY SYSOBJECTS.NAME, SYSCOLUMNS.COLID

OPEN CUR

FETCH NEXT FROM CUR INTO @VAR1, @VAR2

WHILE @@FETCH_STATUS = 0

BEGIN

SET @VAR3=N'IF EXISTS( SELECT 1 FROM '+@VAR1+' WHERE '+@VAR2+' LIKE '''+

'%'+@search_str+'%'+''' )INSERT INTO #FTS_TABLE SELECT '

+''''+UPPER(@VAR1+'('+''+@VAR2)+')'+' HAS VALUE '''+' ,'+

@VAR2+','+'''CONTAINING '''+','''+UPPER(@search_str)+''' FROM '+@VAR1+

' WHERE '+@VAR2+' LIKE '+''''+'%'+@search_str+'%'+''''

EXEC SP_EXECUTESQL @VAR3

IF @@ROWCOUNT>0

set @counter=@counter+1

FETCH NEXT FROM CUR INTO @VAR1, @VAR2

END

CLOSE CUR

DEALLOCATE CUR

IF @counter=0

BEGIN

IF CHARINDEX('-',HOST_NAME())<>0

PRINT 'Sorry '+REPLACE(HOST_NAME(),SUBSTRING(HOST_NAME(), CHARINDEX('-',HOST_NAME()), 12),'')+

', no records matching your search string '''+upper(@search_str)+''''+CHAR(13)+'found in database '+

@dbname+CHAR(13)+'Please try again with other strings.'

ELSE

PRINT 'Sorry '+HOST_NAME()+

', no records matching your search string '''+UPPER(@search_str)+''''+CHAR(13)+'found in database '+

@dbname+CHAR(13)+'Please try again with other strings.'

END

ELSE

SELECT distinct TABLE_NAME 'TABLE(COLUMN)', CONTENT 'VALUE'

FROM #FTS_TABLE WHERE TABLE_NAME NOT LIKE '%#FTS_TABLE%'