Tags

, ,


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 ”?”’

 

<% 
    ‘####################################################### 
    ‘ 
    ‘ 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 
%>
Advertisements