Advertisement
Willcode4cash

Transfer SQL objects between schemas

Feb 9th, 2017
142
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 0.56 KB | None | 0 0
  1. USE your_db_name
  2.  
  3. DECLARE schema_table CURSOR FOR
  4. SELECT
  5.     t.name AS table_nm,
  6.     s.name AS schema_nm
  7. FROM
  8.     sys.tables t inner join sys.schemas s ON t.schema_id = s.schema_id
  9. WHERE
  10.     s.name = 'offending schema name'
  11. ORDER BY
  12.     t.name
  13.  
  14. DECLARE @tbl_nm nvarchar(50),
  15.         @sch_nm nvarchar(50)
  16.  
  17. OPEN schema_table
  18. FETCH NEXT FROM schema_table INTO @tbl_nm, @sch_nm
  19.  
  20. WHILE @@FETCH_STATUS = 0  
  21.     BEGIN  
  22.  
  23.     EXEC('ALTER SCHEMA dbo TRANSFER ' + @tbl_nm)
  24.    
  25.     FETCH NEXT FROM schema_table INTO @tbl_nm, @sch_nm
  26.     END  
  27. CLOSE schema_table
  28. DEALLOCATE schema_table
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement