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:
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