Quickly delete and recreate multiple indexes, views, statistics when a column changes

Question

I have a "StoreNumber" column in my "Project" table that I want to change to "NOT NULL". I recently cleared all the old data so that there are no null entries. However, when I execute the following statement, it fails due to several dependencies on different views, indexes, and statistics

ALTER TABLE [Project] ALTER COLUMN StoreNumber VARCHAR(9) NOT NULL GO 

What is the fastest way to delete all of these views / indexes / statistics, then run the alter statement and then re-create all the views / indexes / statistics? I know that I could copy all the drop and create statements one by one, but I would prefer to generate the script in one request.

On the other hand, why does SQL Server care if I make the column more restrictive? The data does not contain zeros, and I do not change the data type or the size of the columns. How would this type of change ever violate a dependent view, index, or statistics? I am sure that there are sound arguments that I do not see, but I would like to give an example.

Answer 1

Just thinking; will it work if you set the default value first? (do not check the syntax itself)

 ALTER TABLE Project ADD CONSTRAINT col_sn_def DEFAULT '' FOR StoreNumber; GO 

Answer 2

Listed below are a few indices. Note that the final statement does not contain a comma.

 DROP INDEX [index1_1] ON [schema].[table1], [index1_2] ON [schema].[table1], [index2_1] ON [schema].[table2], [index3_1] ON [schema].[table3], ...n, [lastIndexToDrop] ON [schema].[tableName] 

Drop View looks like this. Pay attention to the semicolon to complete the statement.

 DROP VIEW [schema].[view1], [schema].[view2]; 

Currently, I only deal with indexes in my application. To quickly recreate indexes, I read the .sql file into code and execute it in an ExecuteNonQuery call. If I had the views to consider, I would follow the same method of reading from a file to a command to execute with ExecuteNonQuery.

https://msdn.microsoft.com/en-us/library/ms173492.aspx

https://msdn.microsoft.com/en-us/library/ms176118.aspx