Prev: How to tie table to schema?
Next: Compatibility issue???
From: mat on 8 Jul 2010 04:41
Does anyone know of a way to move all objects in a db from schema dbo to
another schema?

From: Erland Sommarskog on 8 Jul 2010 06:38
mat (mat(a)notarealdotcom.adr) writes:
> Does anyone know of a way to move all objects in a db from schema dbo to
> another schema?

In theory:

SELECT 'ALTER SCHEMA newschema TRANSFER dbo.' + quotename(name)
FROM sys.objects
WHERE schema_id = 1

Excute and run result. But before you go ahead, beware of that in practice
it is a lot more difficult.

For starters you should probably add a WHERE clause to the SELECT, so
that you don't include object types like constraints, that will change
schema with their parent object.

If you use user-defined types or XML schema collections, you would
need to run a separate SELECT for these, as they are in a different
catalog view. But what happens if you try change the schema of a type
or a schema collection which is in use, I don't know. But if you don't
have any of these, it may not be an issue.

There is also the qustion about stored procedures, functions and views.
If you have a stored procedure that goes:

SELECT ... FROM tbl

This procedure will continue to look for dbo.tbl, unless you change
the default schema to the new for the procedure owner.

And if the procedure has

SELECT ... FROM dbo.tbl

You will have of course to change the code.

So the gist of this is that this is a fairly complex operation, at least
in the general case, and there could be reason to question whether it
is worth the effort at all. But a lot depends on what actually is in
your database; it may be simpler than what I have shown here.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: How to tie table to schema?
Next: Compatibility issue???