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 search term you specify on line 9 of this code (see the line that says Set @search_str = 'jones').
To use:
Just edit line 9 to put in your search term (replace the "jones") 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='jones'
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('CHAR', 'VARCHAR','TEXT','nvarchar','uniqueidentifier')--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 STRING '''+' ,'+
@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%'