Using ArcPy to get all database names within server?

Question

I am trying to populate a folder with ArcSDE database connection files for all the databases within a server instance. I need to be able to do this for several of our servers.

Although I know within SQL Server how to get a list of DB names from the sys.databases table, using the arcpy function CreateDatabaseConnection I cannot specify "sys" or "master" to make the connection. Therefore, I need to know the name of a database currently in the server. I am not sure how to make the check without hard-coding a name.

import arcpy
arcpy.env.overwriteOutput = True
#Input the location where the database connection files will be stored
connectionLocation = "F:/FolderName"
#Get the list of servers
arcpy.CreateDatabaseConnection_management(out_folder_path= arcpy.env.scratchFolder,out_name= "GIS_USERCONFIG" ,database_platform="SQL_SERVER",\
 instance = "XXXXXXX" ,account_authentication="DATABASE_AUTH",username="user",password="password",\
 save_user_pass="SAVE_USERNAME",database= "GIS_USERCONFIG",schema="#",version_type="TRANSACTIONAL",version="dbo.DEFAULT",date="#")
sdeConn = arcpy.ArcSDESQLExecute(arcpy.env.scratchFolder + "/GIS_USERCONFIG.sde")
serverListSQL = sdeConn.execute("Select name from userservers")
serverList = []
for i in range(0,len(serverListSQL)):
 serverList.append(serverListSQL[i][0])
#Make a folder for each server instance and build the connection files for all DBs in the server instance
for server in serverList:
 folder = connectionLocation + "/" + server
 if arcpy.Exists(folder):
 print server + " folder already created"
 else:
 try:
 arcpy.CreateFolder_management(connectionLocation, server)
 print "Created Folder " + server
 except:
 print "failed to create folder " + server
 #
 arcpy.CreateDatabaseConnection_management(out_folder_path= arcpy.env.scratchFolder,out_name= server ,database_platform="SQL_SERVER",\
 instance = server ,account_authentication="OPERATING_SYSTEM_AUTH",\
 database= "master",schema="#",version_type="TRANSACTIONAL",version="dbo.DEFAULT",date="#")
 sdeConn = arcpy.ArcSDESQLExecute(arcpy.env.scratchFolder + "/" + server + ".sde")
 dbListSQL = sdeConn.execute("Select name from sys.databases")
 dbList = []
 for i in range(0,len(serverListSQL)):
 dbList.append(dbListSQL[i][0])
 for db in dbList:
 arcpy.CreateDatabaseConnection_management(out_folder_path= folder ,out_name= db,database_platform="SQL_SERVER",\
 instance = server ,account_authentication="OPERATING_SYSTEM_AUTH",\
 database= db,schema="#",version_type="TRANSACTIONAL",version="dbo.DEFAULT",date="#")

Basically my issue is with the 2nd call for the function "CreateDatabaseConnection_management" since it needs a database that exists in the database (and I could not use "master or sys"). If you open this as a tool in arcmap, the input is a drop-down box that populates choices based on the databases in the chosen server instance. Is there a way to do this in the code?

Answer 1

You may use ListWorkspaces method (once you make the connection) to list the existing databases within SDE:

Lists all of the workspaces within the set workspace. Search conditions can be specified for the workspace name and workspace type to limit the list that is returned.

workspaces = arcpy.ListWorkspaces("*", "SDE")
 for workspace in workspaces:
 print workspace