SQL Server 2005 Schema from C#

  • Thread starter Thread starter JPS
  • Start date Start date
J

JPS

I need to know how to connect to a SQL Server 2005 server, that
contains multiple databases.
I need to be able to read in the collection of Databases and get the
tables in each and the properties of each field in each table. I have
done this in VB6, but I cannot figure out how to do this in C#
 
JPS said:
I need to know how to connect to a SQL Server 2005 server, that
contains multiple databases.
I need to be able to read in the collection of Databases and get the
tables in each and the properties of each field in each table. I have
done this in VB6, but I cannot figure out how to do this in C#
Click to expand...

The traditional way must be:
sp_databases
sp_tables
sp_columns

Here are an old example using the two first:

using System;
using System.Data;
using System.Data.SqlClient;

class MainClass
{
public static void Main(string[] args)
{
SqlConnection con = new
SqlConnection("server=ARNEPC2;Integrated Security=SSPI;database=master");
con.Open();
SqlCommand cmd = new SqlCommand("sp_databases", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader rdr = cmd.ExecuteReader();
while(rdr.Read()) {
string dbnam = (string)rdr[0];
Console.WriteLine("Database=" + dbnam);
SqlConnection con2 = new
SqlConnection("server=ARNEPC2;Integrated Security=SSPI;database=" + dbnam);
con2.Open();
SqlCommand cmd2 = new SqlCommand("sp_tables", con2);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.Add("@table_type", "'TABLE'");
SqlDataReader rdr2 = cmd2.ExecuteReader();
while(rdr2.Read()) {
string tblnam = (string)rdr2[2];
Console.WriteLine(dbnam + " " + tblnam);
}
con2.Close();
}
con.Close();
}
}

Alternatively you could use INFORMATION_SCHEMA.

Arne
 
Look at the GetSchema method on the SqlConnection class.

Arne Vajhøj said:
JPS said:
I need to know how to connect to a SQL Server 2005 server, that
contains multiple databases.
I need to be able to read in the collection of Databases and get the
tables in each and the properties of each field in each table. I have
done this in VB6, but I cannot figure out how to do this in C#
Click to expand...

The traditional way must be:
sp_databases
sp_tables
sp_columns

Here are an old example using the two first:

using System;
using System.Data;
using System.Data.SqlClient;

class MainClass
{
public static void Main(string[] args)
{
SqlConnection con = new
SqlConnection("server=ARNEPC2;Integrated Security=SSPI;database=master");
con.Open();
SqlCommand cmd = new SqlCommand("sp_databases", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader rdr = cmd.ExecuteReader();
while(rdr.Read()) {
string dbnam = (string)rdr[0];
Console.WriteLine("Database=" + dbnam);
SqlConnection con2 = new
SqlConnection("server=ARNEPC2;Integrated Security=SSPI;database=" + dbnam);
con2.Open();
SqlCommand cmd2 = new SqlCommand("sp_tables", con2);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.Add("@table_type", "'TABLE'");
SqlDataReader rdr2 = cmd2.ExecuteReader();
while(rdr2.Read()) {
string tblnam = (string)rdr2[2];
Console.WriteLine(dbnam + " " + tblnam);
}
con2.Close();
}
con.Close();
}
}

Alternatively you could use INFORMATION_SCHEMA.

Arne
Click to expand...
 
Arne,
What does INFORMATION_SCHEMA. do and what should I replace in this
code?

Thanks,
John

JPS said:
I need to know how to connect to a SQL Server 2005 server, that
contains multiple databases.
I need to be able to read in the collection of Databases and get the
tables in each and the properties of each field in each table. I have
done this in VB6, but I cannot figure out how to do this in C#The traditional way must be:
Click to expand...
sp_databases
sp_tables
sp_columns

Here are an old example using the two first:

using System;
using System.Data;
using System.Data.SqlClient;

class MainClass
{
public static void Main(string[] args)
{
SqlConnection con = new
SqlConnection("server=ARNEPC2;Integrated Security=SSPI;database=master");
con.Open();
SqlCommand cmd = new SqlCommand("sp_databases", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader rdr = cmd.ExecuteReader();
while(rdr.Read()) {
string dbnam = (string)rdr[0];
Console.WriteLine("Database=" + dbnam);
SqlConnection con2 = new
SqlConnection("server=ARNEPC2;Integrated Security=SSPI;database=" +dbnam);
con2.Open();
SqlCommand cmd2 = new SqlCommand("sp_tables", con2);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.Add("@table_type", "'TABLE'");
SqlDataReader rdr2 = cmd2.ExecuteReader();
while(rdr2.Read()) {
string tblnam = (string)rdr2[2];
Console.WriteLine(dbnam + " " + tblnam);
}
con2.Close();
}
con.Close();
}

}Alternatively you could use INFORMATION_SCHEMA.

Arne
Click to expand...
 
JPS said:
What does INFORMATION_SCHEMA. do and what should I replace in this
code?
Click to expand...

SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

vil get tables and columns - I can not find one for databases.

You can call them just like any other queries.

Arne
 
Back
Top