Querying a table in any database

30 Jun 2005 11:48 AM

Shrikant Patil

Hi gurus,

I am trying to create a stored proc, which accepts a database name as
parameter.
This is then supposed to query a table on a field
(db_version.current_version) in "that database", and check if the value in
that field is = '1.0.1.1'

If that matches , stored proc should return '1'. In all other cases, it
should return '0' (other cases could be , no table/field with name
db_version.current_version present in the specified database, table present
but field not present etc) .

I have tried using INFORMATION_SCHEMA.COLUMNS, but not getting a good query
to achieve the above result.

I appreciate any help given ! .. Thanks in advance.

--
regds,
Shrikant Patil
MCDBA, MCSE
Pune - INDIA

30 Jun 2005 12:17 PM

JT

You may need to resort to dynamic sql. Look at the Exec function.

Show quote

"Shrikant Patil" <ShrikantPa***@discussions.microsoft.com> wrote in message
news:4466448C-1CFC-4221-87F3-7E3AB9CEA933@microsoft.com...
> Hi gurus,
>
> I am trying to create a stored proc, which accepts a database name as
> parameter.
> This is then supposed to query a table on a field
> (db_version.current_version) in "that database", and check if the value in
> that field is = '1.0.1.1'
>
> If that matches , stored proc should return '1'. In all other cases, it
> should return '0' (other cases could be , no table/field with name
> db_version.current_version present in the specified database, table
present
> but field not present etc) .
>
> I have tried using INFORMATION_SCHEMA.COLUMNS, but not getting a good
query
> to achieve the above result.
>
> I appreciate any help given ! .. Thanks in advance.
>
> --
> regds,
> Shrikant Patil
> MCDBA, MCSE
> Pune - INDIA

30 Jun 2005 12:37 PM

Jacco Schalkwijk

You can create the procedure as a system stored procedure in the master
database. Than you can call it from any database, and it will use the
current database as it's context. Or you could call it in an explicit
database context with EXEC databasename..procedurename

--
Jacco Schalkwijk
SQL Server MVP

Show quote

"Shrikant Patil" <ShrikantPa***@discussions.microsoft.com> wrote in message
news:4466448C-1CFC-4221-87F3-7E3AB9CEA933@microsoft.com...
> Hi gurus,
>
> I am trying to create a stored proc, which accepts a database name as
> parameter.
> This is then supposed to query a table on a field
> (db_version.current_version) in "that database", and check if the value in
> that field is = '1.0.1.1'
>
> If that matches , stored proc should return '1'. In all other cases, it
> should return '0' (other cases could be , no table/field with name
> db_version.current_version present in the specified database, table
> present
> but field not present etc) .
>
> I have tried using INFORMATION_SCHEMA.COLUMNS, but not getting a good
> query
> to achieve the above result.
>
> I appreciate any help given ! .. Thanks in advance.
>
> --
> regds,
> Shrikant Patil
> MCDBA, MCSE
> Pune - INDIA