InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 11:27:30 GMT20http://www.sqlservercentral.com/Forums/Topic651976-1476-1.aspxI made some minor changes to the code since i had two problems. First, if the schema where not dbo it did not work and if the text i searched for where to long or to long in an varchar field it did not find anything: The changed code looks like this: set nocount onDECLARE @TEXT VARCHAR(500)SET @TEXT = 'test nisse, test malte, hejsan på dig du galde . vad gör dunu då'DECLARE @TABLES TABLE([id] INT IDENTITY(1,1), TableName VARCHAR(500), SchemaName varchar(255),ColumnName VARCHAR(500))INSERT INTO @TABLES(TableName, SchemaName,ColumnName)SELECT O.[NAME], ss.name,C.[NAME]FROM SYSOBJECTS OJOIN SYSCOLUMNS C ON C.ID = O.IDjoin sys.objects SOon so.object_id=o.idjoin sys.schemas SSon ss.schema_id= so.schema_idWHERE O.XTYPE = 'U' AND C.XTYPE NOT IN ( 127 --bigint , 173 --binary , 104 --bit , 61 --datetime , 106 --decimal , 62 --float , 34 --image , 56 --int , 60 --money , 108 --numeric , 59 --real , 58 --smalldatetime , 52 --smallint , 122 --smallmoney , 189 --timestamp , 48 --tinyint , 36 --uniqueidentifier , 165 --varbinary )ORDER BY O.[NAME], C.[NAME]IF EXISTS (SELECT NAME FROM TEMPDB.DBO.SYSOBJECTS WHERE NAME LIKE '#TMPREPORT%')BEGIN DROP TABLE #TMPREPORTENDCREATE TABLE #TMPREPORT(COUNTER INT, TABLENAME VARCHAR(500), COLUMNNAME VARCHAR(500))DECLARE @CNTR bigINT, @POS bigINT, @TableName VARCHAR(500), @SchemaName varchar(255),@ColumnName VARCHAR(500), @SQL VARCHAR(8000)SELECT @POS = 1, @CNTR = MAX([ID]), @TableName = '', @ColumnName = ''FROM @TABLESWHILE @POS <= @CNTRBEGIN SELECT @TableName = TableName, @schemaname = schemaname,@ColumnName = ColumnName FROM @TABLES WHERE [ID] = @POS SELECT @SQL = 'SELECT COUNT(*), ''' + @schemaname + '.' + @TABLENAME + ''' [TABLE],''' + @COLUMNNAME + '''[COLUMN] FROM ' + @schemaname + '.' + @TableName + ' WHERE CAST(' + @ColumnName + ' AS NVARCHAR(4000)) LIKE N''%' + @TEXT + '%''' BEGIN TRY INSERT INTO #TMPREPORT(COUNTER, TABLENAME, COLUMNNAME) EXEC(@SQL) END TRY BEGIN CATCH PRINT @@ERROR PRINT @SQL END CATCH SELECT @POS = @POS + 1ENDSELECT * FROM #TMPREPORT WHERE COUNTER > 0DROP TABLE #TMPREPORTThanks for the code! Really nice and it did the trick fo rme when trying to figure out how VM ware really link their tables.Fri, 28 Aug 2009 07:33:10 GMTmandarinboyhttp://www.sqlservercentral.com/Forums/Topic651976-1476-1.aspxi was really hoping that i would find an article on this subject in SQLServerCentral and thank goodness i found your article, great!you just saved me many hours of hunting!excellent post!Wed, 05 Aug 2009 07:51:59 GMTMarius Elshttp://www.sqlservercentral.com/Forums/Topic651976-1476-1.aspxYou helped me out a bunch with this! Thank you much!Mon, 08 Jun 2009 16:56:53 GMTsmoodyhttp://www.sqlservercentral.com/Forums/Topic651976-1476-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Miscellaneous/65769/">Find Text in all columns of all tables in a Database</A>[/B]Fri, 06 Feb 2009 13:55:21 GMTjwalker8680