批量修改SQLServer中Table的owner
今天在用ER/Studio生成数据库时发现导入的库Owner不正确,在网上找到以下代码可以批量修改SQLServer的owner:
DECLARE @T_Name VARCHAR(100),@T_Owner VARCHAR(50),@SQL VARCHAR(500),@T_Old VARCHAR(50),@T_New VARCHAR(50)
SET @T_Old='OldOwner'
SET @T_New='dbo'
DECLARE t_owner CURSOR FOR
SELECT [NAME],User_NAME(uid) AS UserName FROM SYSOBJECTS WHERE XTYPE<>'S'
OPEN t_owner
FETCH NEXT FROM t_owner INTO @T_Name,@T_Owner
WHILE @@FETCH_STATUS=0
BEGIN
IF UPPER(@T_Owner)=UPPER(@T_Old)
BEGIN
SET @SQL=' sp_changeobjectowner '''+@T_Old+'.'+@T_Name+''','''+@T_New+''''
--PRINT @SQL
EXEC(@SQL)
END
FETCH NEXT FROM t_owner INTO @T_Name,@T_Owner
END
CLOSE t_owner
DEALLOCATE t_owner
SET @T_Old='OldOwner'
SET @T_New='dbo'
DECLARE t_owner CURSOR FOR
SELECT [NAME],User_NAME(uid) AS UserName FROM SYSOBJECTS WHERE XTYPE<>'S'
OPEN t_owner
FETCH NEXT FROM t_owner INTO @T_Name,@T_Owner
WHILE @@FETCH_STATUS=0
BEGIN
IF UPPER(@T_Owner)=UPPER(@T_Old)
BEGIN
SET @SQL=' sp_changeobjectowner '''+@T_Old+'.'+@T_Name+''','''+@T_New+''''
--PRINT @SQL
EXEC(@SQL)
END
FETCH NEXT FROM t_owner INTO @T_Name,@T_Owner
END
CLOSE t_owner
DEALLOCATE t_owner
简要分析一下,这段sql执行原理并不复杂,先从SysObjects表中取出Table名,然后使用sp_changeobjectowner存储过程来修改owner,表名是它的第一个参数,新的Owner名是第二个参数。