Sql Server change fill factor value for all indexes by tsql

sql tsql azure bacpac fillfactor
danielUrrero picture danielUrrero · May 14, 2013 · Viewed 15.8k times · Source

I have to expoet my DB into a bacpac file to import it into Azure. When I try to export I get an error because any indexes have a fillFactor value.

I've found how to set a fillFactor value for all indexes but I can't specify 0, the value have to be between 1 an 100. If I change the value in the management studio I can set it to 0.

The problem is that I have got lots of indexes to change and I would like to change the fillFactor value to all of them trough tsql.

Any ideas?.

Thanks.

Answer

Nick Kavadias picture Nick Kavadias · Apr 28, 2017

something simpler for all tables in a single database:

   select 'ALTER INDEX ALL ON ['
   + s.name+ '].['+ o.name+'] REBUILD WITH (FILLFACTOR = 99)' 
   from sys.objects o
   inner join sys.schemas s on o.schema_id = s.schema_id
   where type='u' and is_ms_shipped=0

generates statements you can then copy & execute.

Related questions

Insert values statement can contain only constant literal values or variable references in SQL Data Warehouse

Consider this table: CREATE TABLE t (i int, j int, ...); I want to insert data into a table from a set of SELECT statements. The simplified version of my query is: INSERT INTO t VALUES ((SELECT 1), (SELECT 2), ...); The real query …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More