February 10, 2009 at 3:26 pm
Write a SQl or change the Schema Names of all the tables who has a Schema Name of DBO to Training .
E.x You have Many tables created with Schema of DBO. Now i dont want to see any tables with Schema of DBO. All the table names should be not dbo. but should be training.
February 10, 2009 at 3:32 pm
Unless all your code is written without schema names in queries, updates, etc., you'll need to do far more than just rename them all.
I don't think there's a way to just rename a schema and have it work. sp_rename only works on objects that are in sys.objects, and schemas aren't one of those.
You're probably going to have to rebuild the database pretty much from the ground up. I could be wrong, but I will be surprised if I am.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 10, 2009 at 4:08 pm
AFAIK, changing the schema name of an object cannot be done: you would have to DROP the object and then recreate it with the new schema name.
And as Gus points out, then you have to find all of the reference to the object and change their SQL code too.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 10, 2009 at 4:39 pm
You can use
ALTER SCHEMA Trainning TRANSFER dbo.tablename;
You will lose the permissions though 🙁
* Noel
February 10, 2009 at 5:48 pm
Cool tip, Noel! I stand corrected... 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 11, 2009 at 7:19 am
noeld (2/10/2009)
You can useALTER SCHEMA Trainning TRANSFER dbo.tablename;
You will lose the permissions though 🙁
Didn't know about that one. But that still leaves you with a whole database of code to review.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2010 at 1:14 pm
Try this:
/* Schema fix all tables.sql
By: MJW 02/05/2010
Desc: change all tables from one schema to another
*/
declare @sql varchar(8000), @table varchar(1000), @oldschema varchar(1000), @newschema varchar(1000)
set @oldschema = 'dbo'
set @newschema = 'Training'
while exists(select * from sys.tables where schema_name(schema_id) = @oldschema)
begin
select @table = name from sys.tables
where object_id in(select min(object_id) from sys.tables where schema_name(schema_id) = @oldschema)
set @sql = 'alter schema ' + @newschema + ' transfer ' + @oldschema + '.' + @table
exec(@sql)
end
edit: changed schema strings to fit the original post for clarity
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply