THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Alberto Ferrari

Visio does not handle Schema? Let's force it to do!

I love VISIO to make database modeling, it gives very nice and colorful diagrams that I use for the documentation.

I hate VISIO as it is really far from perfect, among all the others one thing it lacks is the ability to generate a database using SCHEMA information.

As I like schemas I decided to find a way to bypass VISIO limitations, here it is:

I create table names in VISIO adding the schema to their name (so "Customers" is created as "Config.Customers"). Then I use all the colorful and funny tools to model the database and, after I have generated and run the script for creating the DB (which creates ALL the tables in the schema "dbo") I run this script that changes all the table names removing the schema information, creates the necessary schemas and transfer the tables to their correct one. Et voil√†, I can still love VISIO, even with this (totally absurd) limitation.

--  This code analyzes all the tables in a database, removes the schema information
--  from the name and transfer each table to its proper schema
Declare @TableSchema VarChar (100);     -- Will hold the table Schema
Declare @TableFullName VarChar(100);    -- Will hold the table full name
Declare @TableName VarChar (100);       -- Will hold the table name without the schema
Declare @SqlCommand NVarChar(100);      -- Temporary for issuing SQL commands

--   Traverse the tables searching for those containing a dot in the name
    TableFullName = '[' + Name + ']',
    TableSchema = SubString (Name, 0, charindex ('.', Name)),
    TableName = SubString (Name, charindex ('.', Name) + 1, 100)
Into #TempTables
From Sys.Tables
Where charindex ('.', Name) > 0

--  A cursor used to traverse the table names
Declare Tables_Cursor CURSOR FOR 
    Select * From #TempTables;

Open Tables_Cursor;
Fetch Next From Tables_Cursor Into @TableFullName, @TableSchema, @TableName;
While @@Fetch_Status = 0 Begin
    --  If the schema does not exists then create it
    If Not Exists (Select * from Sys.Schemas where name = @TableSchema) Begin
        Select @SqlCommand = 'Create Schema ' + @TableSchema;
        Exec sp_executesql @SqlCommand;
    --  Rename the table, removing its schema definition
    Exec sp_rename @TableFullName, @TableName
    --  Transfer the table to its desider schema
    Select @SqlCommand = 'alter schema ' + @TableSchema + ' Transfer ' + @TableName
    Exec sp_executesql @SqlCommand;

    Fetch Next From Tables_Cursor Into @TableFullName, @TableSchema, @TableName;

Deallocate Tables_Cursor;
Drop Table #TempTables


Published Wednesday, September 6, 2006 9:48 AM by AlbertoFerrari
Filed under: ,


No Comments
New Comments to this post are disabled

About AlbertoFerrari

Alberto Ferrari is a Business Intelligence consultant. He his interests lie in two main areas: BI development lifecycle methodologies and performance tuning of ETL and SQL code. His main activities are with SSIS and SSAS for the banking, manufacturing and statistical sectors. He is also a speaker in international conferences like European PASS Conference and PASS Summit.
Privacy Statement