Get all User Databases

When I execute the sp_databases proc all I get are the databases that the
logged in user has rights to. I want a stored procedure that will return
just the names of all the non-system databases that I can populate a
dropdown list with. I really don't want to add the user to every database
or give them an Administrator role just to see the user table names. Any
help on this would be much appreciated.
JohnHi, John
Use something like this:
SELECT name FROM master.dbo.sysdatabases
WHERE HAS_DBACCESS(name) = 1
AND name NOT IN ('master','tempdb','model','msdb')
ORDER BY name
Razvan|||John,
Try:
SELECT NAME FROM MASTER..SYSDATABASES
WHERE NAME NOT IN ('MASTER','MSDB','TEMPDB','MODEL','DISTR
IBUTOR')
HTH
Jerry
"john wright" <riley_wright@.hotmail.com> wrote in message
news:%23jo41JN0FHA.2212@.TK2MSFTNGP15.phx.gbl...
> When I execute the sp_databases proc all I get are the databases that the
> logged in user has rights to. I want a stored procedure that will return
> just the names of all the non-system databases that I can populate a
> dropdown list with. I really don't want to add the user to every database
> or give them an Administrator role just to see the user table names. Any
> help on this would be much appreciated.
>
> John
>|||>> I want a stored procedure that will return just the names of all the
You can query the INFORMATION_SCHEMA.SCHEMATA view or the sysdatabases
system table to the the list of all databases. You can avoid the master,
model, msdb, mssqlweb, tempdb, Pubs & Northwind databases in the WHERE
clause to get the list of non-system databases.
Anith|||Great. This works just fine.
John
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ulawJVN0FHA.3956@.TK2MSFTNGP09.phx.gbl...
> You can query the INFORMATION_SCHEMA.SCHEMATA view or the sysdatabases
> system table to the the list of all databases. You can avoid the master,
> model, msdb, mssqlweb, tempdb, Pubs & Northwind databases in the WHERE
> clause to get the list of non-system databases.
> --
> Anith
>sql