How do I change db schema to dbo

I imported a bunch of tables from an old sql server (2000) to my 2008 database. All the imported tables are prefixed with my username, for example: jonathan.MovieData. In the table properties it lists jonathan as the db schema. When I write stored procedures I now have to include jonathan. in front of all the table names which is confusing.

How do I change all my tables to be dbo instead of jonathan?

Current result: jonathan.MovieData

Desired result: dbo.MovieData

227419 次浏览
小开
最佳答案 
ALTER SCHEMA dbo TRANSFER jonathan.MovieData;

参见 ALTER SCHEMA。

广义语法: 

ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName;
小开

Ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/0a760138-460e-410a-a3c1-d60af03bf2ed. htm

ALTER SCHEMA SCHEMA _ name TRANSFER secable _ name

小开 
USE MyDB;
GO
ALTER SCHEMA dbo TRANSFER jonathan.MovieData;
GO

档号: ALTER SCHEMA

小开

可以运行以下命令，它将为所有表生成一组 ALTER sCHEMA 语句:

SELECT 'ALTER SCHEMA dbo TRANSFER ' + TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'jonathan'

然后，您必须在查询分析器中复制并运行语句。

这里有一个旧的脚本，我认为它也可以通过更改对象所有者来实现这一点。2008年还没试过呢。

DECLARE @old sysname, @new sysname, @sql varchar(1000)


SELECT
@old = 'jonathan'
, @new = 'dbo'
, @sql = '
IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND TABLE_SCHEMA = ''' + @old + '''
)
EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''


EXECUTE sp_MSforeachtable @sql

从 this site台拿到的。

如果需要，它还谈到了对存储进程执行相同的操作。

小开

这样做是为了个人:

修改模式 dbo 传输 jathan.MovieData

小开

我刚刚把这个贴到了一个类似的问题: 在 sqlserver2005中，如何在不丢失任何数据的情况下更改表的“模式”？

对赛义德的优秀答案 轻微的的改进。

我添加了一个 exec 来实现这段代码的自执行，并在顶部添加了一个联合，这样我就可以更改两个表和存储过程的模式:

DECLARE cursore CURSOR FOR




select specific_schema as 'schema', specific_name AS 'name'
FROM INFORMATION_SCHEMA.routines
WHERE specific_schema <> 'dbo'


UNION ALL


SELECT TABLE_SCHEMA AS 'schema', TABLE_NAME AS 'name'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA <> 'dbo'






DECLARE @schema sysname,
@tab sysname,
@sql varchar(500)




OPEN cursore
FETCH NEXT FROM cursore INTO @schema, @tab


WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER SCHEMA dbo TRANSFER [' + @schema + '].[' + @tab +']'
PRINT @sql
exec (@sql)
FETCH NEXT FROM cursore INTO @schema, @tab
END


CLOSE cursore
DEALLOCATE cursore

I too had to restore a dbdump, and found that the schema wasn't dbo - I spent hours trying to get Sql Server management studio or visual studio data transfers to alter the destination schema... I ended up just running this against the restored dump on the new server to get things the way I wanted.

小开

将表从 dbo 模式移动到 MySchema:

 ALTER SCHEMA MySchema TRANSFER dbo.MyTable


将表从 MySchema 移动到 dbo 模式:

 ALTER SCHEMA dbo TRANSFER MySchema.MyTable
小开

您可以批量更改多个数据库对象的模式，如本文所述:

How to change schema of all tables, views and stored procedures in MSSQL

小开

我有一个类似的问题，但是我的模式中有一个反斜杠。在这种情况下，包括架构周围的括号。

ALTER SCHEMA dbo TRANSFER [DOMAIN\jonathan].MovieData;
小开

将 SQLServer 作为 SA 帐户打开，然后单击以下查询之后的新查询

然后点击执行，它将回滚所有拥有的模式回到 SA 帐户

alter authorization on schema::[db_datareader] to [dbo]
alter authorization on schema::[db_datareader] to [db_datareader]
alter authorization on schema::[db_datawriter] to [dbo]
alter authorization on schema::[db_datawriter] to [db_datawriter]
alter authorization on schema::[db_securityadmin] to [dbo]
alter authorization on schema::[db_securityadmin] to [db_securityadmin]
alter authorization on schema::[db_accessadmin] to [dbo]
alter authorization on schema::[db_accessadmin] to [db_accessadmin]
alter authorization on schema::[db_backupoperator] to [dbo]
alter authorization on schema::[db_backupoperator] to [db_backupoperator]
alter authorization on schema::[db_ddladmin] to [dbo]
alter authorization on schema::[db_ddladmin] to [db_ddladmin]
alter authorization on schema::[db_owner] to [dbo]
alter authorization on schema::[db_owner] to [db_owner]
小开

如果要一次更改所有表、视图和过程，请使用此代码。

BEGIN
DECLARE @name NVARCHAR(MAX)
, @sql NVARCHAR(MAX)
, @oldSchema NVARCHAR(MAX) = 'MyOldSchema'
, @newSchema NVARCHAR(MAX) = 'dbo'
        

DECLARE objCursor CURSOR FOR
SELECT  o.name
FROM    sys.objects o
WHERE   type IN ('P', 'U', 'V')


OPEN    objCursor
FETCH NEXT FROM objCursor INTO @name
WHILE   @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER SCHEMA '+ @newSchema +' TRANSFER '+ @oldSchema + '.' +  @name
BEGIN TRY
exec(@sql)
PRINT 'Success: ' + @sql
END TRY
BEGIN CATCH
PRINT 'Error executing: ' + @sql
END CATCH
FETCH NEXT FROM objCursor INTO @name
END
CLOSE   objCursor
DEALLOCATE  objCursor

END

它会执行这样的代码

ALTER SCHEMA dbo TRANSFER MyOldSchema.xx_GetUserInformation

提交答案