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!