Overview: 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%' |
SQL Server >