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
 

Originally appeared on: http://sqlblog.com/blogs/alberto_ferrari/archive/2006/09/06/visio-does-not-handle-schema-let-s-force-it-to-do.aspx