Sql Server change fill factor value for all indexes by tsql -

i have expoet db bacpac file import azure. when try export error because indexes have fillfactor value.

i've found how set fillfactor value indexes can't specify 0, value have between 1 100. if change value in management studio can set 0.

the problem have got lots of indexes change , change fillfactor value of them trough tsql.

any ideas?.

thanks.

this isn't straight t-sql way of doing it. though generate pure t-sql solution can apply db.

your results may vary depending on db... example poor referential integrity might make bit trickier..

also comes @ own risk disclaimer :-)

1. get db want migrate ssdt project

http://msdn.microsoft.com/en-us/library/azure/jj156163.aspx http://blogs.msdn.com/b/ssdt/archive/2012/04/19/migrating-a-database-to-sql-azure-using-ssdt.aspx

this nice way migrate schema azure regardless... it's way better creating bacpac file.. fixing... exporting...fixing.. etc... recommend doing anytime want migrate db azure

for fillfactor fixes used find , replace remove fillfactors generated schema files... luckily db using had them set 90 easy solution wide find , replace (ctrl-shift-f)... if yours vary can use regex find features of visual studio find fillfactors , remove them indexes.

i'm not great @ regex think works

with \((.)*fillfactor(.)*\) 

at point you'll have fix additional exceptions around azure compliance.. links provided describe how go doing this

2. now you're @ point have ssdt project that's azure sql compliant.

here comes @ own risk part

i used these scripts remove fk, pk, , unique constraints db.

while(exists(select 1 information_schema.table_constraints constraint_type in  ('foreign key', 'primary key', 'unique'))) begin     declare @sql nvarchar(2000)     select top 1 @sql=('alter table ' + table_schema + '.[' + table_name     + '] drop constraint [' + constraint_name + ']')     information_schema.table_constraints     constraint_type in  ('foreign key', 'primary key', 'unique')     exec (@sql) end   declare @qry nvarchar(max); select @qry =  (select  'drop index [' + ix.name + '] on [' + object_name(id) + ']; '  sysindexes ix   ix.name not null , ix.origfillfactor <> 0 xml path('')); exec sp_executesql @qry 

i because afaik way remove fill factor option drop , re-create index. comes cascading set of issues :-/ pk's fill factors need fk's dropped etc.... there's smarter way don't remove fk's , pk's , @ dependency trees...

3. now go azure compliant ssdt project , schema comparison of project against db... create script recreates fk's, pk's, , unique constraints (without fill factor).... @ point can click "update" or can click button right of update generate script can use... armed

   • the script above remove fks, pks, , unique.
   • the script created ssdt
   • ample testing , review of said scripts ensure nothing missed

you should able update current db azure compliant schema

additional thoughts:

in case fill factors on production db weren't doing useful. created default thing do. in case fill factors might important don't remove them on non azure production box without knowing consequences.

there's additional things consider when doing production system... example might cause mirroring delays , might cause log files grow in way aren't anticipating. both matter if you're applying directly production...

it'd nice if setting them fill factor 100 worked :-/

there's 3rd party tools out there (so i've heard) can use migrate azure...

another option use https://sqlazuremw.codeplex.com/

use create schema that's azure compliant , uses bcp copy data.

but if want make current schema azure compliant can create bacpac file upload azure worked me 1 time i've had it.

edit: azure v12 supports fill factors