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 sql-sqlsvr-sprocs@sqladvice.com list that provided the syntax.
I encourage everyone looking for SQL help to check out the lists on
SQLAdvice.com http://sqladvice.com/lists/category.aspx?c=32
Change Stored procedures in sql 2005
'Note this is the raw query
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 = 'CHANGE_ME_Username'
It would create this kind of output.
SCHEMA dbo TRANSFER steveschofield.spAuthors1
ALTER SCHEMA dbo TRANSFER steveschofield.spAuthors2
ALTER SCHEMA dbo TRANSFER steveschofield.spAuthors3
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