InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 15:48:19 GMT20http://www.sqlservercentral.com/Forums/Topic1063575-391-1.aspxThank You! Nice script too.Any ways, you answered my question. I can't just update the sys.indexes table to change future rebuild/reorganize FILLFACTORs. :-) I tried.PatMon, 14 Feb 2011 08:47:17 GMTbustellhttp://www.sqlservercentral.com/Forums/Topic1063575-391-1.aspxThank You! Nice script too.Any ways, you answered my question. I can't just update the sys.indexes table to change future rebuild/reorganize FILLFACTORs. :-) I tried.PatMon, 14 Feb 2011 08:46:43 GMTbustellhttp://www.sqlservercentral.com/Forums/Topic1063575-391-1.aspxSys.indexes isn't even a table. It's a view. The real system tables are hidden, completely undocumented and changing them is the fastest way yo really mess up your database beyond recovery.Mon, 14 Feb 2011 08:42:58 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1063575-391-1.aspxno you can't...all sys.* items are read only views; MS wanted to remove the ability to update meta data tables directly, because too many times an unintended consequence resulted in corrupt databases when you could do stuff like that in SQL 2000;you'll need to simply use the meta data to generate the scripts for your indexes to specifically include a fill factor;at the bottom of this post is a script i wrote to script out your indexes(it does not create/recreate them...only scripts them); change the case statement near the bottom for the situation where the fill factor is not included in the definition to have your new default. you cna then tweak the resulting script so that tables you know that need more or less of a fill factor.see if this gets you what you are after:[code]--1. get all indexes from current db, place in temp tableselect tablename = object_name(i.id), tableid = i.id, indexid = i.indid, indexname = i.name, i.status, isunique = indexproperty (i.id,i.name,'isunique'), isclustered = indexproperty (i.id,i.name,'isclustered'), indexfillfactor = indexproperty (i.id,i.name,'indexfillfactor')into #tmp_indexesfrom sysindexes iwhere i.indid > 0 and i.indid < 255 --not certain about thisand (i.status & 64) = 0 --existing indexes--add additional columns to store include and key column listsalter table #tmp_indexes add keycolumns varchar(4000), includes varchar(4000)go--################################################################################################--2. loop through tables, put include and index columns into variablesdeclare @isql_key varchar(4000), @isql_incl varchar(4000), @tableid int, @indexid intdeclare index_cursor cursor forselect tableid, indexid from #tmp_indexes open index_cursorfetch next from index_cursor into @tableid, @indexidwhile @@fetch_status <> -1begin select @isql_key = '', @isql_incl = '' select --i.name, sc.colid, sc.name, ic.index_id, ic.object_id, * --key column @isql_key = case ic.is_included_column when 0 then case ic.is_descending_key when 1 then @isql_key + coalesce(sc.name,'') + ' DESC, ' else @isql_key + coalesce(sc.name,'') + ' ASC, ' end else @isql_key end, --include column @isql_incl = case ic.is_included_column when 1 then case ic.is_descending_key when 1 then @isql_incl + coalesce(sc.name,'') + ', ' else @isql_incl + coalesce(sc.name,'') + ', ' end else @isql_incl end from sysindexes i INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.indid AS int) AND ic.object_id=i.id) INNER JOIN sys.columns AS sc ON sc.object_id = ic.object_id and sc.column_id = ic.column_id where i.indid > 0 and i.indid < 255 and (i.status & 64) = 0 and i.id = @tableid and i.indid = @indexid order by i.name, case ic.is_included_column when 1 then ic.index_column_id else ic.key_ordinal end if len(@isql_key) > 1 set @isql_key = left(@isql_key, len(@isql_key) -1) if len(@isql_incl) > 1 set @isql_incl = left(@isql_incl, len(@isql_incl) -1) update #tmp_indexes set keycolumns = @isql_key, includes = @isql_incl where tableid = @tableid and indexid = @indexid fetch next from index_cursor into @tableid,@indexid endclose index_cursordeallocate index_cursor--remove invalid indexes,ie ones without key columnsdelete from #tmp_indexes where keycolumns = ''--################################################################################################--3. output the index creation scriptsset nocount on--separatorselect '---------------------------------------------------------------------'--create index scripts (for backup)SELECT 'CREATE ' + CASE WHEN ISUNIQUE = 1 THEN 'UNIQUE ' ELSE '' END + CASE WHEN ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END + 'INDEX [' + INDEXNAME + ']' +' ON [' + TABLENAME + '] ' + '(' + keycolumns + ')' + CASE WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 1 AND INCLUDES = '' THEN '' WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON)' WHEN INDEXFILLFACTOR <> 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ')' WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES <> '' THEN ' INCLUDE (' + INCLUDES + ') WITH (ONLINE = ON)' ELSE ' INCLUDE(' + INCLUDES + ') WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ', ONLINE = ON)' ENDFROM #tmp_indexeswhere left(tablename,3) not in ('sys', 'dt_') --exclude system tablesorder by tablename, indexid, indexnameset nocount off--drop table #tmp_indexes[/code]Mon, 14 Feb 2011 08:40:42 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1063575-391-1.aspxI have a database that is getting too large for a simple index rebuild periodically. As a result I am developing a plan to update statistics, reorganize or rebuild based on fragmentation and when the last statistics update happened.My question is, can I just update the FILLFACTOR in the sys.indexes table so that the next reorganize or rebuild will pick up the new FILLFACTOR automatically without having to manually do a reorganize to change the FILLFACTOR?PatMon, 14 Feb 2011 08:31:55 GMTbustell