can this be combined into one query

try

declare @max int
declare @r int
declare @sqlstr varchar(8000)
declare @tabschema sysname.@tabname sysname
set @r=1

select table_name,table_schema ,identity(int,1,1) as rowno
 into #temp
 from information_schema.columns
 Where Column_name = 'Screen_name'

Set @max=@@rowcount
select * from #temp into #temp1 where 0 = 1

While @r<= @max
begin
   select @tabname=Table_name,@Tabschema=Table_schema,@r=@r+1
      from #temp
    where rowno=@r
   Set @SqlStr='Insert into #temp Select ['+@tabschema+'].['+@tabname+'] where exists (select screen_name from ['+@tabschema+
                       '].['+@tabname+'] where screen_name=''GARY'''
    Exec(@SQLSTR)
End

Select * from #temp1  

Lowfatspread:

That returned these errors:

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '.'.
Server: Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'into'.
Server: Msg 137, Level 15, State 1, Line 17
Must declare the variable '@tabname'.
Server: Msg 137, Level 15, State 1, Line 20
Must declare the variable '@tabschema'.

>Rather than doing my loop, can this be put into the primary SQL query?  MS SQL 2000

No, you have to have some kind of looping construct.  You need to either query each table with a 'screen_name' column inside a loop or you need to loop thru the candidate tables and dynamically build a Union SQL statement.  I don't see any way around it.

I DO think you can extend this query to only return tables with the 'screen_name' column:
 
    select distinct(name) from sysobjects
   where type = 'U' and category = 1

However, that may simplifies the loop, but does not eliminate it.
I suppose you can move your loop from VB to TSQL (as per lowfatspread) but it's a loop none-the-less.

BTW. In TSQL, this DOES call for a cursor.  I think lowfat's gymnastics to avoid one are counter-productive (no malice intended).

kudos to g_johnson  -- if that works for you, the syntax problems are

declare @tabschema sysname.@tabname sysname

kudos to g_johnson  -- if that works for you, the syntax problems are

declare @tabschema sysname.@tabname sysname

period is a comma

select * from #temp into #temp1 where 0 = 1

move the into #temp1 before the from

There's more syntax after those -- just chiming in on someone else's syntax because of the time delay.

The dynamic SQL is missing a closing parens at the end and a FROM clause.

I took the no cursor challenge and came up with this:

set rowcount 0
declare @tbl  varchar(50)

--get all the tables with the column screen_name
select distinct(sysobjects.name) tblname, null processed
into #temp1
from sysobjects join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.type = 'U' and syscolumns.name = 'screen_name'

set rowcount 1
select * from #temp1 where processed is null
while @@rowcount > 0
begin

select @tbl = tblname
from #temp1

declare @sqlstr varchar(1000)
set @sqlstr = 'select screen_name from ' + @tbl +' where screen_name = ''gary'' '
print @sqlstr
exec(@sqlstr)
if @@rowcount = 0 delete from #temp1 else update #temp1 set processed = 1
select * from #temp1 where processed is null
end

select * from #temp1

itdrms:

When I run that in QA it appears to be in an infinite loop or something.  When I stop the query, I get results tables like:
table_name         processed
table_1               null

screen_name
(no entry)

and that just keeps repeating.

Then I get a "resources low -- some results were dropped" error message

ScottPletcher:

When I run that in QA I get

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'PRINT'.

over and over again

thanks to all of you, btw, for your efforts so far

D'OH, SORRY, left off the closing paren for EXISTS; add a right paren after the last quote after ''GARY'':

SET @sql = N'IF EXISTS(SELECT 1 FROM ' + @name + N' WHERE screen_name = ''GARY'') PRINT ''' + @name + N''''

Not the answer to the loop -- but this "beat the clock" format -- leads to sloppy code.
declare @sqlstr varchar(1000)
should not be inside my loop
and the print statement was a debug line I left in

hmmm, don't know -- worked in my enviroment -- it should alwasy get to the line that either deleted the table from processing or sets the processed flag -- eventually getting no rows left on #temp1.
I defer to the page editor.

thanks g_johnson  for the corrections...

( the dynamic sql str is ok  once the fullstop/comma and into before from  are corrected...
   with MS SQL you don't always need a FROM clause like in most other Database systems )

Set Nocount on
declare @max int
declare @r int
declare @sqlstr varchar(8000)
declare @tabschema sysname,@tabname sysname
set @r=1

select table_name,table_schema ,identity(int,1,1) as rowno
 into #temp
 from information_schema.columns
 Where Column_name = 'Screen_name'

Set @max=@@rowcount
select * into #temp1 from #temp  where 0 = 1

While @r<= @max
begin
   select @tabname=Table_name,@Tabschema=Table_schema,@r=@r+1
      from #temp
    where rowno=@r
   Set @SqlStr='Insert into #temp Select ['+@tabschema+'].['+@tabname+'] where exists (select screen_name from ['+@tabschema+
                       '].['+@tabname+'] where screen_name=''GARY'''
    Exec(@SQLSTR)
End

Select * from #temp1  

Sorry it took me a while to get back to this.

Lowfat:  Latest code returns "incorrect syntax near 'GARY' once for each table in the database.  I tried messing with it, including removing a tic mark on each side of "GARY", but couldn't get it to run w/o error.