bCZbzi1k

1alter proc dbo.admin_MoveSchema ( 
2         @schema varchar(128) 
3        ,@exec bit = 0 
4        ,@storage varchar(2048) = null 
5        ,@filegroup varchar(128) = null 
6        ,@files int = 4 
7) 
8as 
9/*  *************************************************************************** 
10 *  example:   
11    exec dbo.admin_MoveSchema @schema = 'Schema1', @exec = 1 
12 *  *************************************************************************** 
13 */ 
14
15begin try 
16    declare 
17         @msg varchar(255) 
18        ,@separator varchar(255) = replicate('=',128)
19    set nocount on; 
20    if nullif(@storage,'') is null 
21        begin 
22            set @storage = (select top 1 reverse(right(reverse(physical_name),abs(charindex('',reverse(physical_name)) - len(physical_name)))) + '' from sys.database_files where physical_name like '%.mdf') 
23        end; 
24    set @filegroup = rtrim(ltrim(replace(replace(@filegroup,']',''),'[','')));
25    if nullif(@filegroup,'') is null 
26        begin 
27            set @filegroup = 'FG_' + @schema  /* will obviously fail if the schema name is already at max size of 128 */ 
28        end; 
29    declare 
30         @s nvarchar(max) 
31        ,@i int = 1 
32    set @msg = 'Creating Filegroup ' + @filegroup; 
33    print @separator; 
34    print '||' + replicate(' ', 39) + @msg; 
35    raiserror(@separator,0,0) with nowait; 
36    begin try 
37        set @s = ' 
38if not exists (select 1 from sys.filegroups where name = ''' + @filegroup + ''') and ''' + @filegroup + ''' <> ''primary'' 
39    begin 
40        alter database ' + quotename(db_name()) + ' 
41        add filegroup ' + quotename(@filegroup) + '; 
42        print ''' + quotename(@filegroup) + ' added!'' 
43    end 
44        else 
45    begin 
46        print ''' + quotename(@filegroup) + ' exists.'' 
47    end;' 
48        if @exec = 1 
49            begin 
50                exec(@s);
51            end 
52                else 
53            begin 
54                print(@s); 
55            end; 
56        set @msg = 'Creating Files for ' + @filegroup; 
57        print @separator; 
58        print '||' + replicate(' ', 39) + @msg; 
59        raiserror(@separator,0,0) with nowait; 
60        while @i <= @files 
61            begin 
62                set @s = '
63if not exists (select * from sys.sysfiles where name = ''' + @filegroup + '_' + right('00' + rtrim(@i),3) + ''') 
64    begin 
65        alter database [' + db_name() + '] 
66        add file 
67        ( 
68            name = ' + quotename(@filegroup + '_' + right('00' + rtrim(@i),3)) + ', 
69            filename = ''' + @storage + @filegroup + '_' + right('00' + rtrim(@i),3) + '.ndf'', 
70            size = 8mb, 
71            maxsize = unlimited, 
72            filegrowth = 10% 
73        ) 
74        to filegroup ' + quotename(@filegroup) + '; 
75        print ''added file: ' + quotename(@filegroup + '_' + right('00' + rtrim(@i),3)) + ''';
76    end
77        else
78    begin
79        print ''' + quotename(@filegroup + '_' + right('00' + rtrim(@i),3)) + ' exists'';
80    end; '; 
81                if @exec = 1 
82                    begin 
83                        exec(@s); 
84                    end 
85                        else 
86                    begin 
87                        print(@s); 
88                    end; 
89                set @i = @i + 1; 
90            end; 
91    end try 
92    begin catch 
93        print error_message(); 
94    end catch; 
95
96    declare 
97         @schema_name varchar(128) 
98        ,@table_name varchar(128) 
99        ,@column_name varchar(max) -- collection of columns with asc/desc
100        ,@index_name varchar(128) 
101    set @msg = 'Moving tables in the schema ' + quotename(@schema) + ' to filegroup ' + quotename(@filegroup); 
102    print @separator; 
103    print '||' + replicate(' ',18) + @msg; 
104    raiserror(@separator,0,0) with nowait; 
105    declare idxGen cursor local fast_forward
106    for 
107    select 
108         s.name schema_name
109        ,t.name table_name
110        ,i.name index_name 
111        ,stuff((select convert(varchar(max),',') + quotename(c.name) + (case when ic.is_descending_key = 1 then ' desc' else ' asc' end)
112            from sys.columns c
113            inner join sys.index_columns ic on c.column_id = ic.column_id and c.object_id = ic.object_id and ic.index_id = i.index_id
114            where c.column_id = ic.column_id and t.object_id = ic.object_id 
115            order by ic.key_ordinal for xml path('')),1,1,'') column_name 
116    from sys.indexes (nolock) i 
117    inner join sys.tables (nolock) t on t.object_id = i.object_id and i.type_desc = 'CLUSTERED' 
118    inner join sys.schemas (nolock) s on s.schema_id = t.schema_id and t.is_ms_shipped = 0 
119    where s.name = @schema 
120    open idxGen; 
121    fetch next from idxGen into @schema_name,@table_name,@index_name,@column_name; 
122            /* 
123                first match wins, covers unique clustered, clustered and "with" defaults on most tables 
124                for unique and not. I'm not sure if the last 2 every trigger, will check later. 
125             */ 
126    while @@fetch_status = 0 
127        begin 
128                select @s = ' 
129begin try 
130    begin try 
131        create unique clustered index ' + quotename(@index_name) + ' on ' + quotename(@schema_name) + '.' + quotename(@table_name) + ' (' + @column_name + ') 
132        with (drop_existing=on) 
133        on ' + quotename(@filegroup) + '; 
134    end try 
135    begin catch 
136        create clustered index ' + quotename(@index_name) + ' on ' + quotename(@schema_name) + '.' + quotename(@table_name) + ' (' + @column_name + ') 
137        with (drop_existing=on) 
138        on ' + quotename(@filegroup) + '; 
139    end catch; 
140    raiserror(''[Moved/On]: ' + quotename(@schema_name) + '.' + quotename(@table_name) + ' to ' + quotename(@filegroup) + ''', 0,0);
141end try 
142begin catch 
143    print error_message(); 
144    raiserror(''[### MOVE FAILED ###]: ' + quotename(@schema_name) + '.' + quotename(@table_name) + ''', 0,0);
145end catch; '; 
146            if @exec = 1 
147                begin 
148                    exec(@s); 
149                end 
150                    else 
151                begin 
152                    print(@s); 
153                end; 
154            fetch next from idxGen into @schema_name,@table_name,@index_name,@column_name; 
155        end; 
156    close idxGen; 
157    deallocate idxGen; 
158    print @separator; 
159    print '||' + replicate(' ',15) + 'Moved Tables in schema:' + quotename(@schema) + ' to filegroup:' + quotename(@filegroup); 
160    print '||' + replicate(' ',15) + 'If any [### MOVE FAILED ###] were printed above, manual move of that table may be needed.' 
161    print @separator; 
162    print ' '; 
163
164end try 
165begin catch 
166    print 'Error! find out why!'; 
167    throw
168end catch 
169GO 
170     
171if object_id('tempdb..#fileStats','U') is not null
172    begin
173        drop table #fileStats;
174    end;
175
176create table #fileStats (
177     FileId int
178    ,FileGroup int
179    ,TotalExtents bigint
180    ,UserExtents bigint
181    ,Name nvarchar(128)
182    ,FileName nvarchar(2048)
183);
184insert into #fileStats (FileId, FileGroup, TotalExtents, UserExtents, Name, FileName)
185exec('dbcc showfilestats');
186
187select 
188     UserExtents - (select sum(userextents) / count(FileId) from #fileStats where FileGroup = 1) extleft
189    ,UserExtents
190    ,Name
191    ,FileName
192from #fileStats 
193where FileGroup = 1 
194;