Need to select datta from Multiple databases using Query

30 Jun 2005 6:45 PM

Sam

I am trying to do a

Select * from (all tables where the table name begins with a letter P)

I need to export data using Query and do not have access to Enterprise
Manager so I want to select multiple tables using Query Analyzer.

Could some please guide.

Thanks in advance

Sam Commar

30 Jun 2005 7:05 PM

Alejandro Mesa

Try,

use northwind
go

exec sp_msforeachtable 'if parsename(''?'', 1) like ''o%'' select * from ?'
go

AMB

Show quote

"Sam" wrote:

> I am trying to do a
>
> Select * from (all tables where the table name begins with a letter P)
>
> I need to export data using Query and do not have access to Enterprise
> Manager so I want to select multiple tables using Query Analyzer.
>
> Could some please guide.
>
> Thanks in advance
>
> Sam Commar
>
>
>

30 Jun 2005 7:16 PM

Raymond D'Anjou

Alejandro,
You forgot the usual disclaimer about using "undocumented" system stored
procedures.

Show quote

"Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
news:4CBE540A-0D51-47D4-82AF-D78B0B011E0F@microsoft.com...
> Try,
>
> use northwind
> go
>
> exec sp_msforeachtable 'if parsename(''?'', 1) like ''o%'' select * from
> ?'
> go
>
>
> AMB
>
> "Sam" wrote:
>
>> I am trying to do a
>>
>> Select * from (all tables where the table name begins with a letter P)
>>
>> I need to export data using Query and do not have access to Enterprise
>> Manager so I want to select multiple tables using Query Analyzer.
>>
>> Could some please guide.
>>
>> Thanks in advance
>>
>> Sam Commar
>>
>>
>>

30 Jun 2005 7:25 PM

Alejandro Mesa

Raymond,

You are right, but it is never late to do it. Thanks for reminding me.

Sam, the sp [sp_msforeachtable] is an undocumented sp, do not use it in
production code, Microsoft can change its behaviour or not include it in new
versions, without notifying about it.

you can create a cursor to traverse information_schema.tables view and with
the use of dynamic sql, accomplish the same.

AMB

Show quote

"Raymond D'Anjou" wrote:

> Alejandro,
> You forgot the usual disclaimer about using "undocumented" system stored
> procedures.
>
> "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
> news:4CBE540A-0D51-47D4-82AF-D78B0B011E0F@microsoft.com...
> > Try,
> >
> > use northwind
> > go
> >
> > exec sp_msforeachtable 'if parsename(''?'', 1) like ''o%'' select * from
> > ?'
> > go
> >
> >
> > AMB
> >
> > "Sam" wrote:
> >
> >> I am trying to do a
> >>
> >> Select * from (all tables where the table name begins with a letter P)
> >>
> >> I need to export data using Query and do not have access to Enterprise
> >> Manager so I want to select multiple tables using Query Analyzer.
> >>
> >> Could some please guide.
> >>
> >> Thanks in advance
> >>
> >> Sam Commar
> >>
> >>
> >>
>
>
>