Notes for 11/28 Talk - SMO and INFORMATION_SCHEMA

I spoke at the San Diego .NET User Group meetin on 11/28 on SQL Server Metadata (SMO and INFORMATION_SCHEMA). The complete notes should be available on the user group downloads page, but here are the rough notes:

Extract of the PowerPoint outline:

 

Here's my PowerSMO (SMO in PowerShell) script:

#POWERSMO DEMO
#USES POWERSMO - http://www.pluralsight.com/dan/samples/PowerSMO.zip

#SETUP VOODOO
$executionPolicy = get-ExecutionPolicy
set-ExecutionPolicy "Unrestricted"
.\InitPowerSMO.ps1
cls

#CONNECT TO DATABASE
$server = SMO_Server "(local)\SQL2005"
$server.ConnectionContext.SqlExecutionModes = 3 #ExecuteAndCaptureSql
$database = $server.Databases["AdventureWorks"]
$database.FileGroups[0].Files[0].FileName

#LIST TABLES
$database.Tables | %{$_.name}

#CREATE A NEW TABLE
$orders = SMO_Table $database "Orders"
$order_number = SMO_Column $orders "Order Number" (SMO_DataType "Int")
$orders.Columns.Add($order_number)
$customer_name = SMO_Column $orders "Customer Name" (SMO_DataType "Varchar")
$orders.Columns.Add($customer_name)
$value = SMO_Column $orders "Value" (SMO_DataType "Int")
$orders.Columns.Add($value)
$orders.Create()

#SHOW SCHEMA
$orders.Columns | format-Table Name, DataType -auto
$orders.Script()

#CHANGE SOME COLUMNS
$orders.Columns["Value"].DataType = (SMO_DataType "Money")
$orders.Alter()
$orders.Script()

#DROP A COLUMN, THEN THE TABLE
$orders.Columns["Customer Name"].Drop()
$orders.Drop()

cls
$server.ConnectionContext.CapturedSql.Text
#CLEANUP
$server = ''
set-ExecutionPolicy -executionPolicy $executionPolicy

 

Here's the code from my winform sample

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace SMO_Overview
{
    public partial class Main : Form
    {
        Database db;

        public Main()
        {
            InitializeComponent();
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            GetTables(connectionTextbox1.SqlConnectionString);
            Cursor.Current = Cursors.Default;
        }

        private void GetTables(string connectionString)
        {
            ddlTables.Items.Clear();
            SqlConnection connection = new SqlConnection(connectionString);
            Server server = new Server(new ServerConnection(connection));
            db = server.Databases[connection.Database];

            foreach (Table table in db.Tables)
            {
                if (!table.IsSystemObject)
                {
                    if(!string.IsNullOrEmpty(table.Schema))
                        ddlTables.Items.Add(table.Schema + "." + table.Name);
                    else
                        ddlTables.Items.Add(table.Name);
                }
            }
            if (ddlTables.Items.Count > 0)
                ddlTables.SelectedIndex = 0;
        }

        private void ddlTables_SelectedIndexChanged(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                if (ddlTables.SelectedIndex >= 0)
                {
                    string[] tableSelection = ddlTables.Text.Split('.');
                    Table table = db.Tables[tableSelection[1],tableSelection[0]];
                    DataTable columnList = new DataTable();
                    columnList.Columns.Add("Column");
                    columnList.Columns.Add("Datatype");

                    foreach (Column column in table.Columns)
                    {
                        DataRow row = columnList.NewRow();
                        row["Column"] = column.Name;
                        row["Datatype"] = SmoUtil.GetDatatypeString(column);
                        columnList.Rows.Add(row);
                    }
                    dgvColumns.DataSource = columnList;
                }
            }
            catch { }
            Cursor.Current = Cursors.Default;
        }
    }
}

I previously posted most of the INFORMATION_SCHEMA SQL Scripts: Some of my favorite INFORMATION_SCHEMA utility queries

Thanks to Justin Angel, who sent me hundreds of slides about SQL Server 2005 for his upcoming talk on December 4th at the Israeli SQL Server Usergroup. I'm sorry I won't be able to see his talk, it looks like it will be incredible.

2 Comments

Comments have been disabled for this content.