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:
- What is Metadata?
- SMO
- What is it?
- Simple sample - C#
- Data Dictionary Creator
- PowerSMO
- INFORMATION_SCHEMA views
- What are they?
- Sample SQL utility scripts
- Unpivot example
- SubSonic demo
- References
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.