将数据表所属的架构变更为新的架构名

1. DECLARE @name sysname;
   
2. DECLARE @old_schema_name varchar(50)
   
3. DECLARE @new_schema_name varchar(50)
   
4. DECLARE @sql VARCHAR(255)
   
5. SET @old_schema_name = 'kbdb99'  --将老的架构名写引号中
   
6. SET @new_schema_name = 'dbo'             --将新的架构名写引号中
   
7. DECLARE CSR CURSOR
   
8. 
   
9. FOR SELECT t.[name] AS TableName FROM sys.tables AS t,sys.schemas AS s WHERE t.schema_id = s.schema_id AND s.[name] = @old_schema_name
   
10. 
    
11. OPEN csr
    
12.   FETCH NEXT FROM csr INTO @name
    
13.   while (@@FETCH_STATUS=0)
    
14.   BEGIN
    
15.   SET @sql = 'ALTER SCHEMA' + @new_schema_name + 'TRANSFER ' + @old_schema_name + '.' + @name;
    
16.   --PRINT @sql;
    
17.   EXEC (@sql)
    
18.   FETCH NEXT FROM CSR INTO @name
    
19. END
    
20. CLOSE CSR
    
21. DEALLOCATE CSR

复制代码

将数据表所属的架构变更为新的架构名

1. DECLARE @name sysname;
   
2. DECLARE @old_schema_name varchar(50)
   
3. DECLARE @new_schema_name varchar(50)
   
4. DECLARE @sql VARCHAR(255)
   
5. SET @old_schema_name = 'kbdb99'  --将老的架构名写引号中
   
6. SET @new_schema_name = 'dbo'             --将新的架构名写引号中
   
7. DECLARE CSR CURSOR
   
8. 
   
9. FOR SELECT t.[name] AS TableName FROM sys.tables AS t,sys.schemas AS s WHERE t.schema_id = s.schema_id AND s.[name] = @old_schema_name
   
10. 
    
11. OPEN csr
    
12.   FETCH NEXT FROM csr INTO @name
    
13.   while (@@FETCH_STATUS=0)
    
14.   BEGIN
    
15.   SET @sql = 'ALTER SCHEMA' + @new_schema_name + 'TRANSFER ' + @old_schema_name + '.' + @name;
    
16.   --PRINT @sql;
    
17.   EXEC (@sql)
    
18.   FETCH NEXT FROM CSR INTO @name
    
19. END
    
20. CLOSE CSR
    
21. DEALLOCATE CSR

复制代码