Blog Home  Home RSS 2.0 Atom 1.0 CDF  
{Patrick Tam Dot Net} - Changing schema names on Sql Server 2005
Patrick's Web and Moblog About Nothing
 
 Friday, October 26, 2007
In database on Sql Server 2005, I have to change schema names from some username to dbo and I wrote the following sql statements which simply generates alter queries. All you have to do is to change 'Username Here' to actual username, it will generates alter queries for you and then just copy and execute them in query window.

SELECT 'ALTER SCHEMA dbo TRANSFER' + s.Name + '.' + p.Name FROM sys.Procedures p INNER JOIN sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = 'Username Here'  

SELECT 'ALTER SCHEMA dbo TRANSFER' + s.Name + '.' + t.Name FROM sys.Tables t INNER JOIN  
sys.Schemas s on t.schema_id = s.schema_id WHERE s.Name = 'Username Here'  

SELECT 'ALTER SCHEMA dbo TRANSFER' + s.Name + '.' + v.Name FROM sys.Views v INNER JOIN
sys.Schemas s on v.schema_id = s.schema_id WHERE s.Name = 'Username Here'

10/26/2007 3:24:41 PM (Pacific Daylight Time, UTC-07:00)  #       | 
Copyright © 2008 Partick Tam. All rights reserved.