maintenance/mssql/archives/patch-site_stats-modify.sql

   1 /* Delete old default constraints */
   2 DECLARE @sql nvarchar(max)
   3 SET @sql=''
   4
   5 /* IMHO: A DBMS where you have to do THIS to change a default value sucks. */
   6 SELECT @sql= @sql + 'ALTER TABLE site_stats DROP CONSTRAINT ' + df.name + '; '
   7 FROM sys.default_constraints df
   8 JOIN sys.columns c
   9         ON c.object_id = df.parent_object_id
  10         AND c.column_id = df.parent_column_id
  11 WHERE
  12         df.parent_object_id =  OBJECT_ID('site_stats');--
  13
  14 EXEC sp_executesql @sql;
  15
  16 /* Change data type of ss_images from int to bigint.
  17  * All other fields (except ss_row_id) already are bigint.
  18  * This MUST happen before adding new constraints. */
  19 ALTER TABLE site_stats ALTER COLUMN ss_images bigint;
  20
  21 /* Add new default constraints.
  22  * Don't ask me why I have to repeat ALTER TABLE site_stats
  23  * instead of using commas, for some reason SQL Server 2016
  24  * didn't accept it in any other way. Maybe I just don't know
  25  * enough about mssql, but this works.
  26  */
  27 ALTER TABLE site_stats ADD CONSTRAINT col_ss_total_edits DEFAULT NULL FOR ss_total_edits;
  28 ALTER TABLE site_stats ADD CONSTRAINT col_ss_good_article DEFAULT NULL FOR ss_good_articles;
  29 ALTER TABLE site_stats ADD CONSTRAINT col_ss_total_pages DEFAULT NULL FOR ss_total_pages;
  30 ALTER TABLE site_stats ADD CONSTRAINT col_ss_users DEFAULT NULL FOR ss_users;
  31 ALTER TABLE site_stats ADD CONSTRAINT col_ss_active_users DEFAULT NULL FOR ss_active_users;
  32 ALTER TABLE site_stats ADD CONSTRAINT col_ss_images DEFAULT NULL FOR ss_images;