Lv7LZ14n

1class ChangeOwnerToDbo < ActiveRecord::Migration
2  def self.up
3    execute %{
4    
5  CREATE PROCEDURE dbo.change_owner_to_dbo AS
6  BEGIN
7    declare @sqlstring nvarchar(4000)
8    set @sqlstring = 'start'
9    while LEN(@sqlstring) > 0
10    begin
11      set @sqlstring = (
12       select top 1 'EXEC sp_changeobjectowner ''' + TABLE_SCHEMA + '.' + TABLE_NAME + ''', dbo'
13      FROM INFORMATION_SCHEMA.TABLES
14      WHERE
15      OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
16      AND TABLE_TYPE = 'BASE TABLE' 
17      AND TABLE_SCHEMA not like  'dbo' 
18      )
19      if LEN(@sqlstring) > 0
20      begin
21        exec sp_executesql @sqlstring
22        print @sqlstring
23      end 
24    end
25    
26    set @sqlstring = 'start'
27    while LEN(@sqlstring) > 0
28    begin
29      set @sqlstring = (
30       select top 1 'EXEC sp_changeobjectowner ''' + ROUTINE_SCHEMA + '.' + ROUTINE_NAME + ''', dbo'
31      FROM INFORMATION_SCHEMA.ROUTINES
32      WHERE
33      OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME)), 'IsMSShipped') = 0
34      AND ROUTINE_TYPE in ('PROCEDURE', 'FUNCTION')
35      AND ROUTINE_SCHEMA not like  'dbo' 
36      )
37    
38      if LEN(@sqlstring) > 0
39      begin
40        exec sp_executesql @sqlstring
41        print @sqlstring
42      end 
43    end
44  END  
45  }
46  execute "change_owner_to_dbo"
47  end
48  
49  def self.down
50    execute %{ 
51       IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[change_owner_to_dbo]') 
52       AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[change_owner_to_dbo]
53     }
54  end
55  
56end