SQL Server‎ > ‎

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%'

Comments