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