//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Schema Tutorials :: Schema: How do I list the databases on my server?


Schema: How do I list the databases on my server?

SQL Server 
 
The safest way to derive schema information from SQL Server is through system procedures. 
 
EXEC sp_databases 
EXEC sp_helpdb
 
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. 
 
SELECT name 
    FROM master..sysdatabases 
    ORDER BY name
 
And finally, you can use the undocumented sp_msForEachDB: 
 
EXEC sp_msForEachDB 'PRINT ''?'''
 
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
 
SELECT CATALOG_NAME 
    FROM INFORMATION_SCHEMA.SCHEMATA 
    ORDER BY CATALOG_NAME
 
For SQL Server 2005, you can use any of the following: 
 
EXEC sp_databases 
 
EXEC sp_helpdb 
 
SELECT * FROM sys.databases 
 
SELECT * FROM sys.sysdatabases 
 
EXEC sp_msForEachDB 'PRINT ''?'''
 
Finally, you can use SQL-DMO, as demonstrated in this script submitted by Mark Allison
 
<% 
    '####################################################### 
    ' 
    ' Shows how to use the Databases Collection in SQL-DMO 
    ' Created for Aaron Bertrand for publishing on aspfaq.com 
    ' http://www.aspfaq.com/2456 
    ' 
    ' Author: Mark Allison 
    ' http://www.markallison.co.uk/ 
    '  
    ' modified by AB 2005-01-03 
    ' (tested against SQL Server 2000 and SQL Server 2005) 
    ' 
    ' Version: 1.1 
    ' 
    '####################################################### 
 
 
 
' ######################### 

' you will need to modify these parameters: 

' ######################### 
 
    strSQLServer = "server_name" 
    strLogin = "user_name" 
    strPassword = "password" 
 
' ######################### 
 
 
 
    Set oServer = CreateObject("SQLDMO.SQLServer") 
    oServer.Name = strSQLServer 
 
    ' Check the server is alive 
    If oServer.Status <> 1 Then 
        Response.Write "Server " & strSQLServer & " is not running. Exiting." 
    End If 
 
    ' Connect to the SQL Server instance 
    oServer.LoginSecure = False 
    oServer.Connect strSQLServer, strLogin, strPassword 
 
    Response.Write "<table border=1 cellpadding=5 cellspacing=0>" & _ 
        "<tr><th>Name</th><th>Created</th><th>Space Available (MB)</th></tr>" 
 
    ' We're connected to the server let's iterate through the Databases Collection 
    For Each oDB in oServer.Databases 
        Response.Write "<tr><td>" & oDB.Name & "</td>" & _ 
            "<td>" & oDB.CreateDate & "</td>" & _ 
            "<td align=right>" & oDB.SpaceAvailableInMb & "</td></tr>" 
    Next 
 
    ' Disconnect from SQL Server 
    oServer.Disconnect 
    set oServer = nothing 
%>
 
If you get the following error message: 
 
Microsoft SQL-DMO error '800a06ba'  
[SQL-DMO]Service Control Error: The RPC server is unavailable.
 
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
 

Access 
 
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. 
 
<% 
    set fso = CreateObject("Scripting.FileSystemObject") 
 
    dim potentialF(2) 
    potentialF(0) = "/" 
    potentialF(1) = "/db/" 
    potentialF(2) = "/scripts/" 
 
    for i = 0 to 2 
        set fold = fso.getFolder(Server.MapPath(potentialF(i))) 
        for each f in fold.files 
            if right(lcase(f.name), 4) = ".mdb" then 
                response.write potentialF(i) & f.name 
                response.write "<br>" 
            end if 
        next 
    next 
    set fso = nothing 
%>
 
Another approach might be to cycle through the DSNs and determine which ones point to Access databases. See Article #2197 for more information.

Related Articles

Schema: 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?

 

 


Created: 6/16/2003 | Last Updated: 1/4/2005 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (182)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...