>This is what I have been able to come up with so far. It does not work because SELECT does not accept variables in the FROM area. Any ideas on how to get around that?
>
>==========================================
>CREATE PROCEDURE FindInWMSTables
>@StrToFind varchar(50)
> AS
>
>DECLARE @TableName VARCHAR(500),
> @ColName VARCHAR(500)
>
>DECLARE find_cursor CURSOR FOR
>SELECT dbo.sysobjects.name AS TableName, dbo.syscolumns.name AS ColName
>FROM dbo.sysobjects INNER JOIN
> dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id
>WHERE (dbo.sysobjects.xtype = 'U')
>
>OPEN find_cursor
>
>FETCH NEXT FROM find_cursor
>INTO @TableName, @ColName
>
>WHILE @@FETCH_STATUS = 0
>BEGIN
>--========================================
>
> SELECT * FROM @TableName
> WHERE @ColName = @StrToFind
>
>--========================================
> FETCH NEXT FROM find_cursor
> INTO @TableName, @ColName
>END
>
>CLOSE find_cursor
>DEALLOCATE find_cursor
>GO
>
>
>==========================================
>>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.

I forgot about a useful undocumented stored procedure, sp_MSForEachTable. This can be a "cleaner" implementation than coding your own cursor. Details on the sp: http://www.dbazine.com/sql/sql-articles/larsen5