Check For Value In Column In Multiple Table SQL

On occasion, I need to search through many tables to find the one that has a specific value. This is because I have created a unique table per client in the database. Don't worry, I have them aggregated somewhere else, but I am working on data consistency checks. So I can use a cursor with sysobjects to loop through a lot of tables in an automated fashion.

DROP TABLE tempdata..r
CREATE TABLE tempdata..r (tbl VARCHAR(100),email VARCHAR(128))

DECLARE @tbl VARCHAR(100),@sql VARCHAR(4000)

DECLARE a CURSOR FOR 
      SELECT [name] FROM sysobjects WHERE xtype='u'
OPEN a
FETCH NEXT FROM a INTO @tbl
WHILE(@@FETCH_STATUS <> -1)
BEGIN
      SET @sql='INSERT INTO tempdata..r SELECT '''+@tbl+''',Email FROM ['+@tbl+'] WHERE Email='test@yahoo.com'''
      EXEC (@sql)
      FETCH NEXT FROM a INTO @tbl   
END
CLOSE a
DEALLOCATE a

Additional Interesting Articles

PHP Cookie And Authentication
Automatically Redirect People To Different Landing Pages in PHP
PHP All Over Again
SQL Check Existence of Table or Temp Table
t-SQL Cursor
C# DataSet v DataReader

©2008 AndrewKimball.com