0

There are alternative ways to detect database existence, but can the INFORMATION_SCHEMA schema be used instead (such as in the master db, or somewhere else)?

Community
  • 1
  • 1
Tim Partridge
  • 3,365
  • 1
  • 42
  • 52
  • Any particular reason why? Are you looking for something more portable than the SQL Server `sys` views? – Adam Robinson Dec 05 '11 at 16:00
  • No particular reason other than curiosity to know if there is a more portable/supported way of doing it. Seems strange that INFORMATION_SCHEMA would be created for this purpose but not cover this case. – Tim Partridge Dec 05 '11 at 16:04
  • 1
    `INFORMATION_SCHEMA` is intended to cover details on a *per-database* level, not anything outside of that. – Adam Robinson Dec 05 '11 at 16:27

2 Answers2

2

As far as I know, this is not possible with INFORMATION_SCHEMA. As noted in the documentation, INFORMATION_SCHEMA is created on a per database basis, and contains metadata specific to the database it is created in. There is no concept of a "server level" INFORMATION_SCHEMA.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • Thanks for your answer. Yours and Joe's are both correct and say essentially the same thing, so I tossed a coin and you won. Marked as correct. – Tim Partridge Dec 05 '11 at 16:10
  • @TimPartridge: Glad to help. In general, I think most people use "first to answer" as a tie breaker, rather than a coin toss. Either method yields the same result in this case. :-) – Joe Stefanelli Dec 05 '11 at 16:12
2

No; as you can see from all of the views defined in the INFORMATION_SCHEMA namespace, the views contained therein are specific to a single database, so nothing database-agnostic (such as a list of databases) is provided. You'll have to use the ordinary system views as described in your linked question/answer to do that.

Adam Robinson
  • 182,639
  • 35
  • 285
  • 343
  • Thanks for your answer. Yours and Joe's are both correct and say essentially the same thing, so I tossed a coin and you lost. Accepting Joe's answer as correct. Sorry ;) – Tim Partridge Dec 05 '11 at 16:10