How To Change The Schema Of All Tables In A SQL Server Database

Below is the SQL that can be used to change the database schema of all tables in an SQL Server Database. We use it when restoring databases from Production to CRP or Development.

UPDATE: I ran into an issue when running this SQL. For some reason the schema had not been created. So, I added an IF statement to check if the new schema exists. If it doesn’t I create it.

USE JDE_CRP_NEW --TODO: Change this to DB Name

SET NOCOUNT ON
DECLARE @NewSchema nvarchar(256)
DECLARE @OldSchema nvarchar(256)

Set @NewSchema = 'CRPDTA'; --TODO: Change to New Schema 
Set @OldSchema = 'PRODDTA'; --TODO: Change to Old Schema 

--*** Added the below in case the new schema has not been created
IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = @NewSchema)) 
BEGIN
 EXEC ('CREATE SCHEMA [' + @NewSchema + '] AUTHORIZATION [dbo]')
END

--Don't need to change anything below this
DECLARE @TableName nvarchar(256)
DECLARE TableName CURSOR FOR
SELECT DISTINCT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @OldSchema

OPEN TableName

FETCH TableName INTO @TableName

WHILE @@Fetch_Status = 0

 BEGIN

 Print 'Modifying ' + @TableName 
 EXEC('ALTER SCHEMA ' + @NewSchema + ' TRANSFER ' + @TableName)

 FETCH TableName INTO @TableName
 END

CLOSE TableName

DEALLOCATE TableName

One thought on “How To Change The Schema Of All Tables In A SQL Server Database

  1. Hey Barney! Nice tool, but I’ve got a couple of qutoeisns:It bumps the version before it executes the migration, and I’m worried that if an error occurs, it’ll read like the schema is up to date, when it isn’t. I put try/catch/aborts around the migrate(s), and put the revision bump at the end, to see if that would do it it sorta did.I’m still playing with it, so I don’t know if that broke something The other comment is, what about a team scenario, where two members may have updated the schema at the “same time” so to speak? I figured that if this tool works for me, that problem could be solved in the “business logic” (always check the repository prior to adding a migration script, say), but thought it was an interesting thought, or whatever. =]Glad that you won a wii, that’s cool- I just checked out the projects that were submitted, and I’m kinda stoked- looks like there are a couple of them that would make great additions to what I hope to be a release of Model-Glue actionpacks to make RAD more rapid eh. we’ll see.Anyways, thanks for the code, esse!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Previous post Oracle JDEdwards EnterpriseOne in FireFox 20
Next post E1 Pages: What Are They?