Code Example 1:

select 'NJ1' as DB, count(*) as 'Count' -- The first line specifies the column names
from NJ1.dbo.apinpchg                  -- Note the database.owner.object syntax
union all
select 'NJ2',count(*)                  -- Succeeding lists don't need column names
from NJ2.dbo.apinpchg
union all                              -- Use ALL if possible
select 'MA1',count(*)from srv2.MA1.dbo.apinpchg     -- server.database.owner.object
order by 'Count' desc                  -- Output in descending count order
compute sum(count(*))                  -- Total unposted in all three databases

DB   Count 
---- ----------- 
MA1  15
NJ2  12
NJ1  0

     sum
     ==========
     27

Code Example 2:

create view ap_vw
as

select rec_company_code, gl_exp_acct, amt_extended
from NJ1.dbo.apinpcdt c join CONTROL42.dbo.doscodes d
on c.rec_company_code = d.dos_name
where d.region = 1

union all

select rec_company_code, gl_exp_acct, amt_extended
from NJ2.dbo.apinpcdt c join CONTROL42.dbo.doscodes d
on c.rec_company_code = d.dos_name
where d.region = 1

Code Example 3:

create procedure gl_sp @account char(14)
as
set nocount on                           -- Always a good idea in an SP

declare @dbn char(3)

declare	t_cursor cursor for              -- This table holds the names of the
	select company_code              -- databases I want to look at.
	from control42.dbo.ewcomp        -- I can limit them with a where
	order by company_code            -- clause. See BOL.
for read only

create table #t(                         -- Temporary table to hold
db char(3) not null,                     -- the output.
journal char(12) not null,
doc_2 char(16) not null,
apply smalldatetime not null,
account char(14) not null,
description varchar(40)not null,
balance money not null)

/*
Begin looping through the databases. Insert the qualifying rows
from each database into the temporary table. Exec builds the string
and runs it. @dbn holds the database name and is used both 
in the FROM clause and in the temporary table to identify what 
database the rows came from.
Each fetch retrieves a new DB name.
*/
open t_cursor
fetch next from t_cursor into @dbn
while @@fetch_status = 0
begin
    exec ("insert #t(db, journal, doc_2, apply, account, description, balance)
           select '" + @dbn + "', 
           d.journal_ctrl_num,
           document_2,
           dateadd(day,date_applied -729755,'1/1/1999'),
           account_code,
           description,
           balance
           from " + @dbn + ".dbo.gltrx g join " + @dbn + ".dbo.gltrxdet d
           on g.journal_ctrl_num = d.journal_ctrl_num
           where account_code like '" + @account + "' and
           date_applied between 730120 and 730485")
    fetch next from t_cursor into @dbn
end
deallocate t_cursor                      -- We're done, so close the cursor

select * from #t                         -- This is all there is to it  

drop table #t

go

grant execute on gl_sp to public