SQL Server Performance Forum – Threads Archive
Selecting Available DatabasesHI I want to develop a tool in vb which will list all available network sql servers. If any one of the server is selected then i need to select the existing databases from the sql server. Can anyone help me how to do that ? Is any query available for that ? Help needed Cheers Jayaprakash Chandrasekaran
Hi ya, getting the list of sql servers is a job for SQLDMO Dim i As Integer
Dim oNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application
Set oSQLApp = New SQLDMO.Application Set oNames = oSQLApp.ListAvailableSQLServers()
For i = 1 To oNames.Count
Next i getting the list of databases for a server can be done using sp_databases
or you could use SQLDMO again, to connect to the server and get the databases collection Cheers
Just in addition, while almost everybody can issue a SELECT * FROM master..sysdatabases, that doesn’t mean that you also have access to it. ———————–
In general OSQL -L will give you the list of SQL Server on the network including the named instances. Satya SKJ
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />In general OSQL -L will give you the list of SQL Server on the network including the named instances.<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided â€œAS ISâ€ with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes, this will work if the server is not hidden, listens to its default port and is running anyway, imho. [<img src=’/community/emoticons/emotion-4.gif’ alt=’‘ />]<br /><br /><br />———————–<br />–Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />———————–<br />
I also noticed that if you run sp_who under a login that does not have access to all databases on the server, you can also get an error.
Check out SQLPing. It allows you to scan a range of IP addresses and determine sql version. Best of all the C# Source is available for download.
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26 I also found this interesting thread on Finding SQL Server with code snippet on Whidbey SQL Data Source discovery, but I could not reliably get bact to the site.http://www.groupsrv.com/dotnet/viewtopic.php?p=236110 KB 287737 – INF: How to Enumerate Available SQL Servers Using SQLDMO
Good point, but a workaround is to use the information_schema views for that. I would also avoid going to system tables directly. With Yukon tightening the access to system tables and giving a ton of information_schema views or equivalents, it is probably a good idea to not use system tables if you are starting on something new now. Just my 0.02
quote:Originally posted by FrankKalis Just in addition, while almost everybody can issue a SELECT * FROM master..sysdatabases, that doesn’t mean that you also have access to it. ———————–