SQL Server Performance Forum – Threads Archive

Selecting Available Databases

HI 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()
List1.Clear
For i = 1 To oNames.Count
List1.AddItem oNames.Item(i)
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
Twan

Just in addition, while almost everybody can issue a SELECT * FROM master..sysdatabases, that doesn’t mean that you also have access to it. ———————–
–Frank
http://www.insidesql.de
———————–

In general OSQL -L will give you the list of SQL Server on the network including the named instances. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya In general OSQL -L will give you the list of SQL Server on the network including the named instances. <hr noshade size="1">Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center> <hr height="1" noshade id="quote"></blockquote id="quote"> 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=’

‘ />] ———————– –Frank http://www.insidesql.de</a> ———————–

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.
SQLSecurity FAQ
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
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
http://support.microsoft.com:80/support/kb/articles/q287/7/37.asp&NoWebContent=1&NoWebContent=1

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. ———————–
–Frank
http://www.insidesql.de
———————–

Thanks,
— Sri

]]>