В SQL Server 2005, как я могу изменить "схему" таблицы без потери каких-либо данных?

Question

У меня есть таблица, попавшая в схему "db_owner", и она мне нужна в схеме "dbo".

Есть ли скрипт или команда для его переключения?

Answer 1

В SQL Server Management Studio:

1. Щелкните правой кнопкой мыши по таблице и выберите "изменить" (теперь она называется "Дизайн").
   
2. На панели свойств выберите правильную схему владения.

Answer 2

ALTER SCHEMA [NewSchema] TRANSFER [OldSchema].[Table1]

Answer 3

Показать все TABLE_SCHEMA этим выберите:

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 

Вы можете использовать этот запрос, чтобы изменить всю схему для всех таблиц на схему таблицы dbo:

DECLARE cursore CURSOR FOR 

SELECT TABLE_SCHEMA, TABLE_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     
 FETCH NEXT FROM cursore INTO @schema, @tab     
END 

CLOSE cursore     
DEALLOCATE cursore

Answer 4

Простой ответ

sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'

Вам не нужно останавливать все соединения с базой данных, это можно сделать на лету.

Answer 5

Небольшое улучшение превосходного ответа saeid...

Я добавил 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

Мне тоже пришлось восстановить dbdump, и я обнаружил, что схема не была dbo - я потратил часы, пытаясь заставить Sql Server Management Studio или Visual Studio передавать данные, чтобы изменить схему назначения... В итоге я просто запустил это с восстановленной дамп на новый сервер, чтобы получить вещи, как я хотел.

Answer 6

Когда я использую SQL Management Studio, у меня нет опции "Изменить", только "Дизайн" или "Редактировать". Если у вас есть Visual Studio (я проверил VS.NET 2003, 2005 и 2008), вы можете использовать обозреватель сервера для изменения схемы. Щелкните правой кнопкой мыши по таблице и выберите "Таблица проектирования" (2008) или "Определение открытой таблицы" (2003, 2005). Выделите полный столбец "Имя столбца". Затем вы можете щелкнуть правой кнопкой мыши и выбрать "Страницы свойств" или "Свойства" (2008). На странице свойств вы должны увидеть "Владелец" (2003 и 2005) или "Схема" (2008) с раскрывающимся списком возможных схем.

Answer 7

Я использую это для ситуаций, когда группа таблиц должна находиться в другой схеме, в данном случае в схеме dbo.

declare @sql varchar(8000)
;

select
  @sql = coalesce( @sql, ';', '') + 'alter schema dbo transfer [' + s.name + '].[' + t.name + '];'
from 
  sys.tables t
  inner join
  sys.schemas s on t.[schema_id] = s.[schema_id]
where 
  s.name <> 'dbo'
;

exec( @sql )
;

Answer 8

Сначала нужно остановить все соединения с базой данных, изменить владельца таблиц, которые являются db_owner, запустив команду

sp_MSforeachtable @command1="sp_changeobjectowner ""?"",'dbo'"

где? это имя таблицы.