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