How to I get a list of databases?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave Navarro - 23 Jul 2007 19:50 GMT

How can I get a list of databases in an SQL server that I have
permissions for?

I have used ADOX to get a list of tables in a database, but I can't find
any sample ADOX code for getting a list of databases on a server that I
can access.

Thanks!

Reply to this Message

Bob Barrows [MVP] - 23 Jul 2007 20:02 GMT

> How can I get a list of databases in an SQL server that I have
> permissions for?

You can get a list of databases by querying the master.dbo.sysdatabases
table. Depending on the version of SQL Server you are using, there are other
ways as well.

Then, to determine which ones you have permissions for, I guess you would
need to loop through the list of names and attempt to open a connection to
each one in turn, catching the error if it fails.

> I have used ADOX to get a list of tables in a database, but I can't
> find any sample ADOX code for getting a list of databases on a server
> that I can access.

That's because ADOX is geared to work with a single database at a time: its
top-level object is the Catalog, i.e., database.

Signature

Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Reply to this Message

Dave Navarro - 23 Jul 2007 23:46 GMT

But don't you have to have permission to access master.dbo.sysdatabases?

I am wondering because if I connect to my ISP's SQL server using
Enterprise Manager, EP will only show me the databases that I own or
have permission to access.

Since I don't have admin rights to their server, I'm wondering how EP
was able to deduce which databases I can access.

--Dave

> > How can I get a list of databases in an SQL server that I have
> > permissions for?
[quoted text clipped - 13 lines]
> That's because ADOX is geared to work with a single database at a time: its
> top-level object is the Catalog, i.e., database.

Reply to this Message

Bob Barrows [MVP] - 23 Jul 2007 23:52 GMT

Same permissions quandary, but EM uses the sp_databases system stored
procedure if I am not mistaken.

> But don't you have to have permission to access
> master.dbo.sysdatabases?
[quoted text clipped - 25 lines]
>> That's because ADOX is geared to work with a single database at a
>> time: its top-level object is the Catalog, i.e., database.

Signature

Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Reply to this Message

Webmaster Forum / ASP / Database Access / July 2007

How to I get a list of databases?