2

Is it possible to change the precision of a decimal column without dropping and recreating the dependencies of it?

I tried the following:

EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

ALTER TABLE dbo.MyTable
    ALTER COLUMN MyColumn DECIMAL(10, 3) NULL

EXEC sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"

When I execute this query I get the error messages:

Msg 5074, Level 16, State 1, Line 8
The object 'MyView' is dependent on column 'MyColumn'.

Msg 5074, Level 16, State 1, Line 8
The index 'MyIndex' is dependent on column 'MyColumn'.

Msg 4922, Level 16, State 9, Line 8
ALTER TABLE ALTER COLUMN MyColumn failed because one or more objects access this column.

Is it possible to change it without dropping and recreating the dependencies?

Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
xeraphim
  • 4,375
  • 9
  • 54
  • 102

1 Answers1

2

We found a way to do this. Although it probably isn't the best solution, it worked for us so if anyone has the same problem, try the following:

In SQL Server Management Studio go to Tools -> Options -> Designer and uncheck the box of "Prevent saving changes that require table re-creation"

Next rightclick the table you want to modify the column datatypes and click on "Design".

In the designer, edit the column datatype to the one you need.

Finally, right-click and choose "Generate Change Script".

What it does is the following:

  • Drop the constraints on the table
  • Create a temp table with the new datatype of the column
  • Readd constraints to the temp table
  • Set the IDENTITY_INSERT on the temp table to ON
  • Copy all data from the old table to the new temp table
  • Set the IDENTITY_INSERT on the temp table to OFF
  • Drop the old table
  • Rename the temp table to the name of the old table
  • Readd the primary key constraint
  • Recreate the indexes
  • Readd the foreign key constraints

Additionally you have to refresh all depending views. You can generate the statements with this script:

SELECT DISTINCT 'EXEC sp_refreshview ''' + s.name + '.' + so.name + '''' AS 'dbo.TABLENAME'
FROM sys.objects AS so 
INNER JOIN sys.sql_expression_dependencies AS sed 
    ON so.object_id = sed.referencing_id 
INNER JOIN sys.schemas AS s
    ON so.schema_id = s.schema_id
WHERE so.type = 'V' AND sed.referenced_id = OBJECT_ID('dbo.TABLENAME');
  • Check the box of "Prevent saving changes that require table re-creation" in Tools -> Options -> Designer

Please be careful about this! See if it really does what you are looking for. Keep in mind that this drops the old table. Test this in a development environment!

xeraphim
  • 4,375
  • 9
  • 54
  • 102
  • Question: Why dropping the table and recreating it with all the constraints is better than dropping the constraint, changing the column and recreating the constraint? – Pred May 23 '16 at 11:59
  • Yeah good question, I've only described the script that the Sql Server Management Studio generates. Since we had to do it on a lot of tables, we needed a way to script it. But you're probably right, it will likely work like this as well – xeraphim May 23 '16 at 12:01