2

I need to change the VARCHAR length from 20 to 25 in a code column in a library table in SQL Server 2014, but SSMS wants to DROP and RE-CREATE all my tables where the library table in question is linked to.

Is there a way to change the VARCHAR length of a PRIMARY KEY without dropping all the tables?

2

As you are increasing the length you can just do an alter column, for example:

CREATE TABLE dbo.T1
    (
      C1 VARCHAR(20) NOT NULL
                     CONSTRAINT PK_T1 PRIMARY KEY ,
      C2 INT
    );
GO

INSERT  INTO dbo.T1
VALUES  ( 'test', 1 ),
        ( 'test2', 2 );
GO

ALTER TABLE dbo.T1 ALTER COLUMN C1 VARCHAR(25) NOT NULL;

Further reading on the ALTER TABLE syntax at https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql

| improve this answer | |
  • I don't think this will work as is, if the column in question has foreign keys that reference it. – RDFozz Mar 27 '17 at 15:14
  • This is true, but OP does not mention FK references. – Nic Mar 27 '17 at 15:21
  • From the OP: "DROP and RE-CREATE all my tables where the library table in question is linked to." (emphasis added). – RDFozz Mar 27 '17 at 15:23
  • I did not extrapolate that as there being a foreign key in place. That's my misunderstanding if that's the case. – Nic Mar 27 '17 at 15:30
  • Agreed - I could be wrong, just how I read the question. Couldn't think of another reason for it to be wanting to drop and recreate other tables.... – RDFozz Mar 27 '17 at 15:53
2

You will want to use the ALTER TABLE command, as in Nic's answer. However, the problem is that there are foreign keys that reference your table.

To get around this, you'll want to script out the foreign keys, drop them to make the change, and then recreate them after you've updated the tables.

Here's a link to a script by Aaron Bertrand for scripting out the command to drop and create your foreign keys.

NOTE: I would also update the column in the other tables that use it, to make sure that all allowed values will fit in those other tables.

| improve this answer | |
-6

Try to uncheck "Deny saving changes which needed recreation of a table". In SQL Managenet St. click on bar, choise Service - Parameters, select Diesigners - Table and Database Constructors. Then unscheck "Deny saving changes which needed recreation of a table".

enter image description here

| improve this answer | |
  • 2
    Unless I'm greatly mistaken, the user isn't looking to have SQL Server drop and recreate the tables; he's trying to avoid this. The change you recommend means that SQL would go ahead and drop and recreate the tables, possibly without adequate warning. You can always get the code SQL would have used to make the change, so you can review it and run it manually, if you like. – RDFozz Mar 27 '17 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.