/* *********************************** PROGRAM TO TRANSFER TABLES TO NEW FILEGROUPS *************************************** Written By : Rod Merritt !!!! USE AT YOUR OWN RISK !!!! !!!! USE AT YOUR OWN RISK !!!! !!!! USE AT YOUR OWN RISK !!!! !!!! THIS PROGRAM BUILDS AND ISSUES DROPS AND ALTER STATEMENTS AGAINST YOUR DATABASES !!!! OVERVIEW You need to read the code and understand what it does to make sure it is right for you. It will dynamically build and issue drop and alter statements against whatever databases are targeted. This program moves all the tables in a database to new filegroups. The reason for having tables in their own filegroups are: 1) this allows table level restore by doing a filegroup recovery with a log restore. 2) if you're not using raid, then this allows placement of the tables on disk to avoid contention 3) it allows SQL Server to use multiple i/o threads during read operations, especially table scans, when you define multiple files per filegroup In our case, the developers created a system with many hundreds of tables on many databases on multiple servers, all on the primary default file. So I needed a way to move them to filegroups with two files in each, to allow table level restores and to make use of parallel i/o with our four processors in each machine. I may move the tables to four-file filegroups in the future, all I have to do is change the names a little and run this program again. This program might also be used to reverse fragmentation which occurs after a period of time by allowing files to autogrow. If you're not using raid, then you might modify the program to place a table's indexes on different files as well. The program uses system stored procedures to avoid any future problems in case the catalogs change. There are print statements throughout which are useful because any commands issued against the databases are printed in the output for review. It may not be the most efficient program possible, but I didn't write it to sell, only to use myself and possibly it can be of use to others in my situation. There are variable declarations which may not be large enough for your installation. If anyone has a question about the SQL, feel free to post a question to this group and I will explain it. PROCEDURE This program will loop through databases and tables within databases and change everything... so be very careful when running it! I have tested it on pubs, northwind, and a copy of our production databases, I have used a design tool to reverse engineer the results and they match the original, and I am almost ready to turn it loose on production. But the usual disclaimer applies ..... USE AT YOUR OWN RISK !!!! Back up the target databases prior to running this program, at least twice if possible, with verification. You need to first fill in the databases which the program is to work on. This is done in the very first cursor definition called 'Cursor for going through databases'. The next cursor called 'Cursor for going through tables within a database' could be modified to work on certain tables within the databases, but it would look for the same tables in every database because of the nested loop design. First this program creates a filegroup and file using the name of the table it is currently working on. The space allocations for new files are derived by multiplying the row length * the row count * 1.5 for each table. The 1.5 is a 'fudge factor' to allow for indexes. Next it determines the existence of any index, clustering ix, or pkey on the table. Then based on what combination of these exists, it will execute one of three different procedures to handle the data move. Even though it may have been more efficient to combine 'if' statements in the last two conditions, I left things separate for programming clarity. CONDITION 1 -- (no ix) or (non-clust ix with a pkey defined on it) or (non-clust ix without pkey defined on it) pkey dosen't matter in this case since it won't be touched if it's not clustering pick the first column in the table definition create a temporary clust ix using this column on the new filegroup drop the temporary clustering ix CONDITION 2 -- (clust ix without pkey defined on it) save the clust ix definition recreate clust ix on new filegroup with 'drop_existing' clause CONDITION 3 -- (clust ix with pkey defined on it) store the pkey definition trace all fkey references to the pkey and store them drop fkeys drop pkey recreate pkey on new filegroup with 'clustering ix' clause recreate the fkeys */ -- ****************************************** VARIABLE DECLARATIONS ******************************************************** set nocount on use master declare @userdb sysname declare @usertab sysname declare @userowner sysname declare @qual_tab_name sysname declare @logical_file_name1 sysname declare @physical_file_name1 sysname declare @logical_file_name2 sysname declare @physical_file_name2 sysname declare @filegroup_name sysname declare @create_pkey varchar(200) declare @create_fkey varchar(200) declare @create_clust varchar(200) declare @colname sysname declare @clustname sysname declare @fkey_constname sysname declare @fkey_usertab sysname declare @fkey_column_name sysname declare @fkey_tab_name sysname declare @fkey_comp_tab_name sysname declare @fkey_name sysname declare @pkey_constname sysname declare @pkey_name sysname declare @pkey_column_name sysname declare @ix_ind varchar(200) declare @ix_name varchar(200) declare @ix_desc varchar(200) declare @clustix_name varchar(200) declare @clust_name varchar(200) declare @clust_column_name varchar(200) declare @execstr nvarchar(300) declare @char_rowlen varchar(10) declare @char_rowcount varchar(10) declare @char_tabsize varchar(20) declare @rowlen int declare @rowcount int declare @tabsize int -- ******************************************** TEMPORARY TABLES ********************************************************* -- Temp table to hold names of tables in a database if object_id ('tempdb..#tmp_tab_names') is not null drop table #tmp_tab_names create table #tmp_tab_names (TABLE_QUALIFIER sysname, TABLE_OWNER sysname, TABLE_NAME sysname, TABLE_TYPE varchar(32), REMARKS varchar(254)) -- tmp tab to hold output from sp_statistics if object_id ('tempdb..#tmp_tab_stats') is not null drop table #tmp_tab_stats create table #tmp_tab_stats (TABLE_QUALIFIER sysname null, TABLE_OWNER sysname null, TABLE_NAME sysname null, NON_UNIQUE smallint null, INDEX_QUALIFIER sysname null, INDEX_NAME sysname null, TYPE smallint null, SEQ_IN_INDEX smallint null, COLUMN_NAME sysname null, COLLATION char(1) null, CARDINALITY int null, PAGES int null, FILTER_CONDITION varchar(128) null) -- tmp tab to hold output from sp_columns if object_id ('tempdb..#tmp_tab_columns') is not null drop table #tmp_tab_columns create table #tmp_tab_columns (TABLE_QUALIFIER sysname null, TABLE_OWNER sysname null, TABLE_NAME sysname null, COLUMN_NAME sysname null, DATA_YTPE smallint null, TYPE_NAME varchar(13) null, PRECIS int null, LENGTH int null, SCALE smallint null, RADIX smallint null, NULLABLE smallint null, REMARKS varchar(254) null, COLUMN_DEF varchar(254) null, SQL_DATA_TYPE smallint null, SQL_DATETIME_SUB smallint null, CHAR_OCTET_LENGTH int null, ORDINAL_POSITION int null, IS_NULLABLE varchar(254) null, SS_DATA_TYPE tinyint null) -- tmp tab to hold output from sp_pkeys if object_id ('tempdb..#tmp_tab_pkey') is not null drop table #tmp_tab_pkey create table #tmp_tab_pkey (TABLE_QUALIFIER sysname null, TABLE_OWNER sysname null, TABLE_NAME sysname null, COLUMN_NAME sysname null, KEY_SEQ smallint null, PK_NAME sysname null) -- tmp tab to hold output from sp_fkeys if object_id ('tempdb..#tmp_tab_fkeys') is not null drop table #tmp_tab_fkeys create table #tmp_tab_fkeys (PKTABLE_QUALIFIER sysname NULL, PKTABLE_OWNER sysname NULL, PKTABLE_NAME sysname NOT NULL, PKCOLUMN_NAME sysname NOT NULL, FKTABLE_QUALIFIER sysname NULL, FKTABLE_OWNER sysname NULL, FKTABLE_NAME sysname NOT NULL, FKCOLUMN_NAME sysname NOT NULL, KEY_SEQ smallint NOT NULL, UPDATE_RULE smallint NULL, DELETE_RULE smallint NULL, FK_NAME sysname NULL, PK_NAME sysname NULL, DEFERRABILITY smallint null) -- tmp tab to hold output from sp_helpindexes if object_id ('tempdb..#tmp_tab_helpindex') is not null drop table #tmp_tab_helpindex create table #tmp_tab_helpindex (index_name sysname NULL, index_description varchar(210) NULL, index_keys varchar(2078) NULL) -- ********************************************** CURSOR DEFINITIONS ****************************************************** -- Cursor for going through databases declare userdb_list cursor for select name from sysdatabases where name in ('pubs') -- Cursor for going through tables within a database declare tab_list cursor for select table_name, table_owner from #tmp_tab_names where table_type = 'TABLE' and table_name <> 'dtproperties' -- Cursor for going through pkey columns declare pkey_list cursor for select pk_name, column_name from #tmp_tab_pkey order by key_seq -- Cursor for going through fkey columns declare fkey_list cursor for select distinct fk_name, fktable_name from #tmp_tab_fkeys -- Cursor for going through fkey columns declare fkey_list3 cursor for select distinct fktable_name from #tmp_tab_fkeys -- Cursor for going through helpindex columns declare helpindex_list cursor for select index_name, index_description from #tmp_tab_helpindex -- Cursor for going through stats columns declare clust_list cursor for select index_name, column_name from #tmp_tab_stats where type = 1 order by seq_in_index -- ********************************************** MAIN PROGRAM LOGIC ***************************************************** -- Loop through databases, tables, pkeys, fkeys and clustered ixs. open userdb_list fetch userdb_list into @userdb WHILE @@FETCH_STATUS = 0 begin -- while more databases delete from #tmp_tab_names print ('use ' + @userdb + ' insert into #tmp_tab_names exec sp_tables') exec ('use ' + @userdb + ' insert into #tmp_tab_names exec sp_tables') open tab_list fetch tab_list into @usertab, @userowner WHILE @@FETCH_STATUS = 0 begin -- while more tables set @qual_tab_name = @userdb + '.' + @userowner + '.' + @usertab print ' ' print '****************************------ working on table ' + @qual_tab_name + ' ------********************************' -- estimate size of table set @execstr = N'use ' + @userdb + N' select @char_rowcount = convert(varchar(10), count(*)) from ' + @usertab exec sp_executesql @execstr, N'@char_rowcount varchar(10) output', @char_rowcount OUTPUT set @execstr = N'use ' + @userdb + N' select @char_rowlen = convert(varchar(10), sum(sc.length)) from syscolumns sc, sysobjects so where sc.id = so.id and so.name = ''' + @usertab + N''' group by sc.id ' exec sp_executesql @execstr, N'@char_rowlen varchar(10) OUTPUT', @char_rowlen OUTPUT set @rowcount = cast(@char_rowcount as int) set @rowlen = cast(@char_rowlen as int) set @tabsize = ((1.5 * @rowlen * @rowcount)/1000)/2 -- divide by # files in filegroup if @tabsize < 1000 set @tabsize = 1000 set @char_tabsize = convert(varchar(20),@tabsize) -- create filegroup and file set @filegroup_name = 'fg_' + @usertab set @logical_file_name1 = 'log1_' + @usertab set @physical_file_name1 = '"d:\mssql7\data\fl1_' + @usertab + '"' set @logical_file_name2 = 'log2_' + @usertab set @physical_file_name2 = '"d:\mssql7\data\fl2_' + @usertab + '"' exec ('alter database ' + @userdb + ' add filegroup ' + @filegroup_name) -- add new fg and file for this table print ('alter database ' + @userdb + ' add file (name = ' + @logical_file_name1 + ', filename = ' + @physical_file_name1 + ', size = ' + @char_tabsize + 'KB, filegrowth = 10%) to filegroup ' + @filegroup_name) exec ('alter database ' + @userdb + ' add file (name = ' + @logical_file_name1 + ', filename = ' + @physical_file_name1 + ', size = ' + @char_tabsize + 'KB, filegrowth = 10%) to filegroup ' + @filegroup_name) print ('alter database ' + @userdb + ' add file (name = ' + @logical_file_name2 + ', filename = ' + @physical_file_name2 + ', size = ' + @char_tabsize + 'KB, filegrowth = 10%) to filegroup ' + @filegroup_name) exec ('alter database ' + @userdb + ' add file (name = ' + @logical_file_name2 + ', filename = ' + @physical_file_name2 + ', size = ' + @char_tabsize + 'KB, filegrowth = 10%) to filegroup ' + @filegroup_name) -- check if table has any indexes, if it does, see if there is a clustering ix or a pkey defined set @ix_ind = 'no_ix' delete from #tmp_tab_helpindex -- record index info exec ('use ' + @userdb + ' insert into #tmp_tab_helpindex exec sp_helpindex ' + @usertab) if (select count(*) from #tmp_tab_helpindex) <> 0 begin set @ix_ind = 'has_ix' set @clustix_name = 'no_clust' set @pkey_name = 'no_pkey' open helpindex_list fetch helpindex_list into @ix_name, @ix_desc WHILE (@@FETCH_STATUS) = 0 begin if (select charindex('nonclustered', @ix_desc)) = 0 set @clustix_name = @ix_name if (select charindex(' primary key', @ix_desc)) <> 0 set @pkey_name = @ix_name fetch helpindex_list into @ix_name, @ix_desc end close helpindex_list end -- CONDITION 1 -- (no ix) or (non-clust ix with a pkey defined on it) or (non-clust ix and no pkey defined) if (@ix_ind = 'no_ix') or ((@ix_ind = 'has_ix') and (@clustix_name = 'no_clust')) begin print '.. CONDITION 1 .. no ix, or no clust ix found for ' + @usertab delete from #tmp_tab_columns exec ('use ' + @userdb + ' insert into #tmp_tab_columns exec sp_columns ' + @usertab + ', ' + @userowner + ', ' + @userdb) set @colname = (select top 1 column_name from #tmp_tab_columns) print ('use ' + @userdb + ' create clustered index temp_ix_for_moving_data on ' + @usertab + ' (' + @colname + ') on ' + @filegroup_name) exec ('use ' + @userdb + ' create clustered index temp_ix_for_moving_data on ' + @usertab + ' (' + @colname + ') on ' + @filegroup_name) print ('use ' + @userdb + ' drop index ' + @usertab + '.temp_ix_for_moving_data') exec ('use ' + @userdb + ' drop index ' + @usertab + '.temp_ix_for_moving_data') end -- CONDITION 1 -- CONDITION 2 -- (clust ix without pkey defined on it) if ((@ix_ind = 'has_ix') and (@clustix_name <> 'no_clust')) and ((@pkey_name = 'no_pkey') or ((@pkey_name <> 'no_pkey') and (@clustix_name <> @pkey_name))) begin print '.. CONDITION 2 .. clust ix without a pkey defined on it was found for table ' + @usertab delete from #tmp_tab_stats -- record clustering ix info exec ('use ' + @userdb + ' insert into #tmp_tab_stats exec sp_statistics ' + @usertab + ', ' + @userowner + ', ' + @userdb) -- create ddl to rebuild clustering ix open clust_list fetch clust_list into @clust_name, @clust_column_name set @create_clust = 'use ' + @userdb + ' create clustered index ' + @clust_name + ' on ' + @usertab + ' (' WHILE @@FETCH_STATUS = 0 begin -- while more clust ix columns set @create_clust = @create_clust + @clust_column_name fetch clust_list into @clust_name, @clust_column_name if @@fetch_status = 0 set @create_clust = @create_clust + ',' else set @create_clust = @create_clust + ') with drop_existing on ' + @filegroup_name end -- while more clust ix columns print @create_clust exec (@create_clust) close clust_list end -- CONDITION 2 -- CONDITION 3 -- (clust ix with pkey defined on it) if ((@ix_ind = 'has_ix') and (@clustix_name <> 'no_clust') and (@pkey_name <> 'no_pkey') and (@clustix_name = @pkey_name)) begin -- put primary and foreign keys into #tmp tables and drop them, this also drops clust ix defined with pkey print '.. CONDITION 3 .. clust ix with pkey defined on it was found for table ' + @usertab delete from #tmp_tab_pkey exec ('use ' + @userdb + ' insert into #tmp_tab_pkey exec sp_pkeys ' + @usertab + ', ' + @userowner + ', ' + @userdb) delete from #tmp_tab_fkeys exec ('use ' + @userdb + ' insert into #tmp_tab_fkeys exec sp_fkeys ' + @usertab + ', ' + @userowner + ', ' + @userdb) if (select count(*) from #tmp_tab_fkeys) <> 0 begin -- if pkey has fkey references, drop fkeys first open fkey_list fetch fkey_list into @fkey_constname, @fkey_usertab WHILE @@FETCH_STATUS = 0 begin -- drop fkeys print ('use ' + @userdb + ' alter table ' + @fkey_usertab + ' drop constraint ' + @fkey_constname ) exec ('use ' + @userdb + ' alter table ' + @fkey_usertab + ' drop constraint ' + @fkey_constname ) fetch fkey_list into @fkey_constname, @fkey_usertab end -- while more fkeys close fkey_list end -- if pkey has fkey references -- now that fkeys are dropped or didn't exist, drop pkey set @pkey_constname = (select top 1 pk_name from #tmp_tab_pkey) print ('use ' + @userdb + ' alter table ' + @usertab + ' drop constraint ' + @pkey_constname ) exec ('use ' + @userdb + ' alter table ' + @usertab + ' drop constraint ' + @pkey_constname ) -- create ddl to rebuild pkey and clustering ix open pkey_list fetch pkey_list into @pkey_name, @pkey_column_name set @create_pkey = 'use ' + @userdb + ' alter table ' + @usertab + ' add constraint ' + @pkey_name + ' primary key clustered (' WHILE @@FETCH_STATUS = 0 begin -- while more pkey columns set @create_pkey = @create_pkey + @pkey_column_name fetch pkey_list into @pkey_name, @pkey_column_name if @@fetch_status = 0 set @create_pkey = @create_pkey + ',' else set @create_pkey = @create_pkey + ') on ' + @filegroup_name end -- while more pkey columns print @create_pkey exec (@create_pkey) close pkey_list -- create ddl to rebuild fkeys, more than one is possible, each with more than one column if ((select count(*) from #tmp_tab_pkey) <> 0) and ((select count(*) from #tmp_tab_fkeys) <> 0) begin -- table had fkeys pointing to it open fkey_list3 fetch fkey_list3 into @fkey_comp_tab_name while @@fetch_status = 0 begin -- process all tables with fks declare fkey_list2 cursor for -- cursor for going through fkey columns for table from outer cursor select fktable_name, fkcolumn_name, fk_name from #tmp_tab_fkeys where fktable_name = @fkey_comp_tab_name order by key_seq open fkey_list2 fetch fkey_list2 into @fkey_tab_name, @fkey_column_name, @fkey_name set @create_fkey = 'use ' + @userdb + ' alter table ' + @fkey_tab_name + ' add constraint ' + @fkey_name + ' foreign key (' WHILE @@FETCH_STATUS = 0 begin -- while more fkey columns set @create_fkey = @create_fkey + @fkey_column_name fetch fkey_list2 into @fkey_tab_name, @fkey_column_name, @fkey_name if @@fetch_status = 0 set @create_fkey = @create_fkey + ',' else set @create_fkey = @create_fkey + ') references ' + @usertab end -- while more fkey columns print @create_fkey exec (@create_fkey) close fkey_list2 deallocate fkey_list2 fetch fkey_list3 into @fkey_comp_tab_name end -- while more tables with fks close fkey_list3 end -- table had fkeys end -- CONDITION 3 fetch tab_list into @usertab, @userowner end -- while more tables close tab_list fetch userdb_list into @userdb end -- while more databases close userdb_list deallocate clust_list deallocate fkey_list deallocate fkey_list3 deallocate pkey_list deallocate helpindex_list deallocate tab_list deallocate userdb_list