Schema: How do I list the databases on my server?
The safest way to derive schema information from SQL Server is through system procedures.
You can also use the system tables, but this is not the recommended approach, since system table functionality could change in a future version of SQL Server.
And finally, you can use the undocumented sp_msForEachDB:
In SQL Server 2000, you can use the INFORMATION_SCHEMA views, however—because of the new support for Schema—this specific view no longer functions the same in SQL Server 2005:
For SQL Server 2005, you can use any of the following:
Finally, you can use SQL-DMO, as demonstrated in this script submitted by Mark Allison:
If you get the following error message:
You probably forgot to replace "server_name" with your actual server name in the sample script.
For a full list of the methods and properties associated with the database object within SQL-DMO, see the reference page SQL-DMO: Database Object.
I suppose you could use Scripting.FileSystemObject to loop through all the potential folders where Access databases might reside, and print out all the occurences of an MDB extension. Note that this will not, obviously, find Access databases that reside on file shares or remote HTTP servers.
Another approach might be to cycle through the DSNs and determine which ones point to Access databases. See Article #2197 for more information.
Related ArticlesSchema: How do I list all the indexes in a database?
Schema: How do I show all the primary keys in a database?
Schema: How do I show all the triggers in a database?
Schema: How do I show the columns for a table?
Schema: How do I show the description property of a column?
Schema: How do I show the parameters for a function or stored procedure?
Schema: How do I show the stored procedures in a database?
Schema: How do I show the tables in a database?
Schema: How do I show the user-defined functions (UDFs) in a database?
Schema: How do I show the views in a SQL Server database?