There are a few ways to get schema information from a SQL server but the easiest and cleanest way must be by using the Microsoft.SqlServer.Management.Smo classes. These are a managed library you can use from within c# or vb.net to manipulate SQL 2005.

You will need to reference the assemblies found in your SQL installation folder under the SDK/Assemblies folder.

Once we have that, we can do the following to get all database, table and column info from the classes:

protected void Page_Load(object sender, EventArgs e)

{

    // Create a Server instance of the database server to connect to.

    // You might need to set it's Credentials property if you are using

    // sql authentication

    Server server = new Server("W-Stefan");

    // Loop through all databases in the db server

    foreach (Database db in server.Databases)

    {

        Response.Write("<h1>" + db.Name + "</h1><hr/>");

        Response.Write("<ul>");

        // Loop through all tables in the database

        foreach (Microsoft.SqlServer.Management.Smo.Table table in db.Tables)

        {

            Response.Write("<li>" + table.Name + ":<br/>");

            // Loop through all columns in the table

            foreach (Column c in table.Columns)

            {

                Response.Write(c.Name + " - " + c.DataType.ToString() + "<br/>");

            }

        }

        Response.Write("</ul>");

    }

}

This will produce something like this on your asp.net page:

ReportServerTempDB  <--- Database

• ChunkData:  <--- Table
  ChunkID - uniqueidentifier
  SnapshotDataID - uniqueidentifier     <--- Column inside the table
  ChunkFlags - tinyint
  ChunkName - nvarchar
  ChunkType - int
  Version - smallint
  MimeType - nvarchar
  Content - image
  
• ExecutionCache:
  ExecutionCacheID - uniqueidentifier
  ReportID - uniqueidentifier
  ExpirationFlags - int
  AbsoluteExpiration - datetime
  RelativeExpiration - int
  SnapshotDataID - uniqueidentifier

This is obviously just a sample of what can be done with the SMO classes and doesn't really serve any useful purpose in its current form.