SQL Server 2005 Schema from C#

[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#

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

JPS wrote:

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.Sql Client;

class MainClass
{
public static void Main(string[] args)
{
SqlConnection con = new
SqlConnection(" server=ARNEPC2; Integrated Security=SSPI;d atabase=master" );
con.Open();
SqlCommand cmd = new SqlCommand("sp_ databases", con);
cmd.CommandType = CommandType.Sto redProcedure;
SqlDataReader rdr = cmd.ExecuteRead er();
while(rdr.Read( )) {
string dbnam = (string)rdr[0];
Console.WriteLi ne("Database=" + dbnam);
SqlConnection con2 = new
SqlConnection(" server=ARNEPC2; Integrated Security=SSPI;d atabase=" + dbnam);
con2.Open();
SqlCommand cmd2 = new SqlCommand("sp_ tables", con2);
cmd2.CommandTyp e = CommandType.Sto redProcedure;
cmd2.Parameters .Add("@table_ty pe", "'TABLE'");
SqlDataReader rdr2 = cmd2.ExecuteRea der();
while(rdr2.Read ()) {
string tblnam = (string)rdr2[2];
Console.WriteLi ne(dbnam + " " + tblnam);
}
con2.Close();
}
con.Close();
}
}

Alternatively you could use INFORMATION_SCH EMA.

Arne

[=?Utf-8?B?RnJhbmNvaXNWaWxqb2Vu?=]

Look at the GetSchema method on the SqlConnection class.

"Arne Vajhøj" wrote:

JPS wrote:

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.Sql Client;

class MainClass
{
public static void Main(string[] args)
{
SqlConnection con = new
SqlConnection(" server=ARNEPC2; Integrated Security=SSPI;d atabase=master" );
con.Open();
SqlCommand cmd = new SqlCommand("sp_ databases", con);
cmd.CommandType = CommandType.Sto redProcedure;
SqlDataReader rdr = cmd.ExecuteRead er();
while(rdr.Read( )) {
string dbnam = (string)rdr[0];
Console.WriteLi ne("Database=" + dbnam);
SqlConnection con2 = new
SqlConnection(" server=ARNEPC2; Integrated Security=SSPI;d atabase=" + dbnam);
con2.Open();
SqlCommand cmd2 = new SqlCommand("sp_ tables", con2);
cmd2.CommandTyp e = CommandType.Sto redProcedure;
cmd2.Parameters .Add("@table_ty pe", "'TABLE'");
SqlDataReader rdr2 = cmd2.ExecuteRea der();
while(rdr2.Read ()) {
string tblnam = (string)rdr2[2];
Console.WriteLi ne(dbnam + " " + tblnam);
}
con2.Close();
}
con.Close();
}
}

Alternatively you could use INFORMATION_SCH EMA.

Arne

[JPS]

Arne,
What does INFORMATION_SCH EMA. do and what should I replace in this
code?

Thanks,
John

On Jan 4, 9:14 pm, Arne Vajhøj <a...@vajhoej.d kwrote:

JPS wrote:

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.Sql Client;

class MainClass
{
public static void Main(string[] args)
{
SqlConnection con = new
SqlConnection(" server=ARNEPC2; Integrated Security=SSPI;d atabase=master" );
con.Open();
SqlCommand cmd = new SqlCommand("sp_ databases", con);
cmd.CommandType = CommandType.Sto redProcedure;
SqlDataReader rdr = cmd.ExecuteRead er();
while(rdr.Read( )) {
string dbnam = (string)rdr[0];
Console.WriteLi ne("Database=" + dbnam);
SqlConnection con2 = new
SqlConnection(" server=ARNEPC2; Integrated Security=SSPI;d atabase=" +dbnam);
con2.Open();
SqlCommand cmd2 = new SqlCommand("sp_ tables", con2);
cmd2.CommandTyp e = CommandType.Sto redProcedure;
cmd2.Parameters .Add("@table_ty pe", "'TABLE'");
SqlDataReader rdr2 = cmd2.ExecuteRea der();
while(rdr2.Read ()) {
string tblnam = (string)rdr2[2];
Console.WriteLi ne(dbnam + " " + tblnam);
}
con2.Close();
}
con.Close();
}

}Alternatively you could use INFORMATION_SCH EMA.

Arne

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

JPS wrote:

What does INFORMATION_SCH EMA. do and what should I replace in this
code?

SELECT * FROM INFORMATION_SCH EMA.TABLES
SELECT * FROM INFORMATION_SCH EMA.COLUMNS

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

You can call them just like any other queries.

Arne

[Marc Scheuner]

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

In VS 2005 (.NET 2.0), you can use the SMO (SQL Management Objects) to
deal with this. They allow you to connect to a given SQL server and
explore all its databases, all the database objects inside that
database, and a lot more.

Add a reference to the "Microsoft.SqlS erver.Smo" assembly to your
project, and then add a "using Microsoft.SqlSe rver.Management .Smo"
clause to your file, and you're ready to go!

I'm sure you'll find plenty of info and tutorials and code samples on
sites like www.codeproject.com and others about how to use SMO in
detail - go explore!

Marc