How do I list all my SQL local instances?

Question

1.00/5 (1 vote)

See more:

I want to create a test program. In this program i want to list all my local sql instances including the sql server name. Windows form app or windows console app. Please help or give me some advice.

What I have tried:

C#

static void Main(string[] args)
        {
            using (System.Data.DataTable sqlSources = 
            SqlDataSourceEnumerator.Instance.GetDataSources())
            {
                foreach (System.Data.DataRow source in sqlSources.Rows)
                {
                    string servername;
                    string version = source["Version"].ToString();
                    string instanceName = source["InstanceName"].ToString();
                    if (!string.IsNullOrEmpty(instanceName))
                     {
                         servername = string.Concat(source["InstanceName"], "\\", 
                         source["ServerName"]);
                     }
                     else
                     {
                         servername = source["ServerName"].ToString();
                         version = source["Version"].ToString();
                         instance = source["InstanceName"].ToString();
                     }
                    Console.WriteLine(" Server Name:{0}", servername);
                    Console.WriteLine("Version:{0}", source[version]);
                    Console.WriteLine("Instance:{0}", source[instanceName]);
                    Console.ReadLine();
                }

Posted 11-Dec-17 22:58pm

Member 13475897

Updated 11-Dec-17 23:26pm

Add a Solution

Comments

CHill60 12-Dec-17 5:14am

What is wrong with the code you have?

Member 13475897 12-Dec-17 5:57am

it is returning the server name but not the instances in the server

phil.o 12-Dec-17 5:25am

Is it possible you have reversed the server and instance names?
It is rather ServerName\\InstanceName imho.

Member 13475897 12-Dec-17 5:57am

Im trying to list the instances in my server, the server name is returning correctly. The version returns as a null and the instance is returning a blank. The version isn't important but i'd like to list all the instances in my server

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2017-12-11T23:26:00

Solution 1

Names must match:

C#

string instanceName = source["InstanceName"].ToString();
...
instance = source["InstanceName"].ToString();

Try

C#

string instanceName = source["InstanceName"].ToString();
...
instanceName = source["InstanceName"].ToString();

Then, use your collected data in your WriteLines:

C#

Console.WriteLine("Server Name:{0}", servername);
Console.WriteLine("Version:{0}",version);
Console.WriteLine("Instance:{0}", instanceName);

Quote:
How would i go about in going futher and displaying the databases in the server's instances

C#

using (SqlConnection con = new SqlConnection(connectionStringToAServerInstance))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("sp_databases", con))
        {
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataReader read = cmd.ExecuteReader();
        while (read.Read())
            {
            Console.WriteLine((string)read["DATABASE_NAME"]);
            }
        }
    }

Posted 11-Dec-17 23:26pm

OriginalGriff

Updated 12-Dec-17 1:46am

v2

Comments

Member 13475897 12-Dec-17 6:04am

The instance name is returning a blank when i put break points in the Console.WriteLine("Instance:{0}", instanceName); part. Want i mean by instance name is i want to list the instances in my local sql server. Currently there is only 1 instance

OriginalGriff 12-Dec-17 6:28am

Well, that makes some sense - if the instance name is blank after this:
string instanceName = source["InstanceName"].ToString();
Then this test fails:
if (!string.IsNullOrEmpty(instanceName))
So you do this again in the else part:
instanceName = source["InstanceName"].ToString();

So you need to look at the rest of "source" and see what you are getting - when I run it on my system I get my server instances. What do you get for ServerName and version?

Member 13475897 12-Dec-17 6:38am

My correct local sql server name and the version is also correct but i saw some websites say that if you use the default instance it will return blank , some comment on this please. ServerName:DESKTOP-FEQ6FKG and version:13.0.1601.5

OriginalGriff 12-Dec-17 7:06am

I've not seen that - my local default dev instance returns:
Server Name:SQLEXPRESS\GRIFF-DESKTOP
Version: 11.0.5058.0
Instance:SQLEXPRESS
(I'm stuck with SQL Server 2012 to match the production side server)

Member 13475897 12-Dec-17 7:35am

thanks helped alot

Member 13475897 12-Dec-17 7:34am

How would i go about in going futher and displaying the databases in the server's instances

OriginalGriff 12-Dec-17 7:46am

Answer updated

Member 13475897 12-Dec-17 9:24am

Thanks

Richard Deeming 13-Dec-17 11:20am

Won't that display the databases on the specified instance, rather than the named instances of SQL Server? :)

OriginalGriff 13-Dec-17 11:38am

The second code will, but that's what he asked for after the main question.

Richard Deeming 13-Dec-17 11:42am

D'Oh!