THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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
--
Select 
    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;
    End;
    --
    --  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;
End;

Deallocate Tables_Cursor;
Drop Table #TempTables
Go

 

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

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.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement