How To Change The Schema Of All Tables In A SQL Server Database
Read Time:1 Min, 2 Sec
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
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!