Posted by: Cirilo Meggiolaro | 12/29/2008

Tip of the day #76 – Retrieving table information with Microsoft.SqlServer.Management.Smo namespace

Tip #75 explained a little bit about the Microsoft.SqlServer.Management.Smo namespace and demonstrated how to connect to a Sql Server instance and retrieve database information.

Today’s tip explores the code to retrieve information about existing tables under a database instance.

How to…

For this example I am using a sample database called School that is part of the Entity Framework tutorials.

  1. Create a Server class instance creating a connection to your Sql Server;
  2. Retrieve a database instance from the databases collection available under the Server class;
  3. Loop through the Tables collection under the database object and display the tables information;
  4. Display the table name and number of rows using the Name and RowCount properties;
  5. Loop through the Columns collection available under each Table object;
  6. Display the column name, data type and if the column is nullable or not using the Name, DataType.Name and Nullable properties.

The code must be similar to the following:

/// Create a server class instance.
Server svr = new Server(@”MYPC\SQLEXPRESS”);

if ((svr != null) && (svr.Databases.Count > 0))
{
    /// Retrieve the database from the server class instance.
    Database db = svr.Databases[“School”];

    if (db != null)
    {
        Console.WriteLine(“Tables information:”);
        Console.WriteLine();

        /// Loop through all tables available in the database
        foreach (Table table in db.Tables)
        {
            /// Display table name and number of rows
            Console.WriteLine(“======================================”);
            Console.WriteLine(“Table name: {0} – Rows: {1}”, table.Name, table.RowCount);
            Console.WriteLine(“======================================”);
            Console.WriteLine();
            Console.WriteLine(“Columns:”);
            Console.WriteLine();

            /// Loop through all existing columns from the current table
            foreach (Column column in table.Columns)
            {

                /// Display column name, data type and if the column accepts null value or not.
                Console.WriteLine(“Column Name: {0}”, column.Name);
                Console.WriteLine(“Data type: {0}”, column.DataType.Name);
                Console.WriteLine(“Nullable: {0}”, column.Nullable.ToString());
                Console.WriteLine();
            }
        }
    }
}

Output:

================================================
Table name: Course – Rows: 10
================================================

Columns:

Column Name: CourseID
Data type: int
Nullable: False

Column Name: Title
Data type: nvarchar
Nullable: False

Column Name: Credits
Data type: int
Nullable: False

Column Name: DepartmentID
Data type: int
Nullable: False

================================================
Table name: CourseGrade – Rows: 40
================================================

Columns:

Column Name: EnrollmentID
Data type: int
Nullable: False

Column Name: CourseID
Data type: int
Nullable: False

Column Name: StudentID
Data type: int
Nullable: False

Column Name: Grade
Data type: decimal
Nullable: True

================================================
Table name: CourseInstructor – Rows: 9
================================================

Columns:

Column Name: CourseID
Data type: int
Nullable: False

Column Name: PersonID
Data type: int
Nullable: False

================================================
Table name: Department – Rows: 4
================================================

Columns:

Column Name: DepartmentID
Data type: int
Nullable: False

Column Name: Name
Data type: nvarchar
Nullable: False

Column Name: Budget
Data type: money
Nullable: False

Column Name: StartDate
Data type: datetime
Nullable: False

Column Name: Administrator
Data type: int
Nullable: True

================================================
Table name: OfficeAssignment – Rows: 9
================================================

Columns:

Column Name: InstructorID
Data type: int
Nullable: False

Column Name: Location
Data type: nvarchar
Nullable: False

Column Name: Timestamp
Data type: timestamp
Nullable: False

================================================
Table name: OnlineCourse – Rows: 4
================================================

Columns:

Column Name: CourseID
Data type: int
Nullable: False

Column Name: URL
Data type: nvarchar
Nullable: False

================================================
Table name: OnsiteCourse – Rows: 6
================================================

Columns:

Column Name: CourseID
Data type: int
Nullable: False

Column Name: Location
Data type: nvarchar
Nullable: False

Column Name: Days
Data type: nvarchar
Nullable: False

Column Name: Time
Data type: smalldatetime
Nullable: False

================================================
Table name: Person – Rows: 34
================================================

Columns:

Column Name: PersonID
Data type: int
Nullable: False

Column Name: LastName
Data type: nvarchar
Nullable: False

Column Name: FirstName
Data type: nvarchar
Nullable: False

Column Name: HireDate
Data type: datetime
Nullable: True

Column Name: EnrollmentDate
Data type: datetime
Nullable: True

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: