Thank you for your response Sergey. Is there a benefit to manually going through the rows using rn, rather than declaring a cursor, as I did in my 2nd entry in this thread?


>It's possible but it may take longer that a little bit.

>SET NOCOUNT ON
>
>DECLARE @StrToFind varchar(50)
>SET @StrToFind = 'Bern'
>
>DECLARE @RecCount int, @i int,
>		@Table_Name varchar(128), @Column_Name varchar(128),
>		@Sql varchar(4000)
>
>-- a table variable to store the list of colums and result of the search		
>DECLARE @ColList TABLE (
>	rn	int IDENTITY,
>	Table_Name varchar(128),
>	Column_Name varchar(128),
>	MatchFound tinyint DEFAULT (0))
>
>-- Get the list of the columns with character data type
>INSERT INTO @ColList (Table_Name, Column_Name)
>SELECT Table_Name, Column_Name FROM INFORMATION_SCHEMA.COLUMNS
>	WHERE data_type LIKE '%char%'
>
>SET @RecCount = @@ROWCOUNT
>
>-- Loop through all the columns and search them
>SET @i = 1
>WHILE @i <= @RecCount BEGIN
>	-- Current table and column
>	SELECT @Table_Name  = Table_Name,
>			 @Column_Name = Column_Name
>		FROM @ColList
>		WHERE rn = @i
>		
>	-- Buils SELECT and execute it
>	SET @Sql =  'DECLARE @MatchFound int ' +
>		' SELECT @MatchFound = 1 WHERE EXISTS ( SELECT * FROM [' + @Table_Name + ']' +
>		' WHERE ' + @Column_Name + ' = ''' + @StrToFind + ''')'
>	
>	EXECUTE(@Sql)
>	--
>	IF @@ROWCOUNT > 0
>		UPDATE @ColList SET MatchFound = 1
>		WHERE rn = @i
>	
>	-- Advance loop to the next column in the list
>	SET @i = @i + 1
>END
>
>-- Return only columns with matches found
>SELECT *
>	FROM @ColList
>	WHERE MatchFound <> 0
>
>

>>I am trying to dissect a legacy database and find where things are put. It is a large database, with about 100 tables. It has little referential integrity in the database itself (mostly being handled in the interface). The people who created it are gone.
>>
>>It would help if I could search all fields in all of the tables for a given string with one command, even if that would take a little while to complete, it would be shorter than me opening each table manually and entering a search command.
>>
>>Is such a search command possible? It should return the name of the table and field where the string would be found.