I was trying to change the ‘schema’ name on an object from ‘username’ to ‘dbo’ in SQL Server 2005.
The script generates code you have to copy, paste and run on the database.
Thanks to the DBA gurus on the email@example.com list that provided the syntax.
I encourage everyone looking for SQL help to check out the lists on
Change Stored procedures in sql 2005
‘Note this is the raw query
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = ‘CHANGE_ME_Username’
It would create this kind of output.
You would run in a new query window, after this refresh SQL Management studio
and the stored procedures would be dbo.spAuthors1, dbo.spAuthors2, dbo.spAuthors3.
Change Table in sql 2005
Here is an article that describes the syntax, it uses the ‘sp_changeobjectowner’
which isn’t real “sql2005’ish” but works.
declare @OldOwner varchar(100) declare @NewOwner varchar(100)
set @OldOwner = ‘OldOwner’ set @NewOwner = ‘NewOwner’
select ‘sp_changeobjectowner ”[‘ + table_schema + ‘].[‘ +
table_name + ‘]”, ”’ + @NewOwner + ”’ go
from information_schema.tables where Table_schema = @OldOwner
13 thoughts on “Change schema name on Tables and Stored procedures in SQL Server 2005”
it would have been more helpful it include a typical sql statement example
this worked like a charm, changed tables’ schemas perfectly…thanks a ton
BEAUTIFUL!, thank you very much, save alot time updating a web database.
It works perfectly,
Thanks a ton, but how can we change de function’s schema?
You can use “ALTER SCHEMA schema_name TRANSFER securable_name”
Thanks a lot
U have given a great solution
For tables and views you can use
SELECT ‘ALTER SCHEMA NewSchema TRANSFER ‘ + TABLE_SCHEMA + ‘.’ + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘OldSchema’
yeah verry cool. That helps me to save a lot of time.
Thanks Daniel !!
Super, Great, saved lots of time changing owner for table and sprocs.
For those who do not quite understand (be sure to change sys.Procedures p
to sys.Tables p) to get your list of table change statements ready to copy from results pane to a new query window, then execute the whole list at once.
If you don’t quite get it (I didn’t without some additional research) there’s just a few more things you need to know…
1). Change sys.Procedures to sys.Tables
2). Change ‘CHANGE_ME_Username’ to the current name of the schema you are trying to rename
3). You don’t need to change anything else
4). Open a New Query pane and paste the script with your modifications into the Script pane and then execute it
5). Do a Select All and copy everything into another (New) Query Pane
6). Execute that
All tables that previously had the old schema name will now have just ‘dbo’ in front of them instead of the old schema name which means they’ll be available to queries using just the table name rather than needed the reference to the old schema
good article. thanks
It is a perfect solution and gave me right path. saved my lot of time.
Thanks Charles it works
SELECT ‘ALTER SCHEMA dbo TRANSFER ‘ + s.Name + ‘.’ + p.Name FROM sys.Tables p
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = ‘CHANGE_ME_USER’