How do I alter a TEXT column on a database table in SQL server?

Question

In a SQL server database, I have a table which contains a TEXT field which is set to allow NULLs. I need to change this to not allow NULLs. I can do this no problem via Enterprise Manager, but when I try to run the following script, alter table dbo.[EventLog] Alter column [Message] text Not null, I get an error:

Cannot alter column 'ErrorMessage' because it is 'text'.

Reading SQL Books Online does indeed reveal you are not allow to do an ALTER COLUMN on TEXT fields. I really need to be able to do this via a script though, and not manually in Enterprise Manager. What are the options for doing this in script then?

Answer 1

Create a new field. Copy the data across. Drop the old field. Rename the new field.

Answer 2

Off the top of my head, I'd say you need to create a new table with the same structure as the existing table but with your text column set to not null and then run a query to move the records from one to the other.

I realize that's sort of a pseudocode answer but I think that's really the only option you've got.

If others with a better grip on the exact TSQL syntax care to supplement this answer, feel free.

Answer 3

I have also used the create new column, copy, then drop method

Answer 4

I would update all the columns with NULL values and set it to an empty string, for example, ''. Then you should be able to run your ALTER TABLE script with no problems. A lot less work than creating a new column.

Answer 5

Try to generate the change script from within Enterprise Manager to see how it is done there

Answer 6

You can use Enterprise Manager to create your script. Right click on the table in EM and select Design. Uncheck the Allow Nulls column for the Text field. Instead of hitting the regular save icon (the floppy), click an icon that looks like a golden scroll with a tiny floppy or just do Table Designer > Generate Change Script from the menu. Save the script to a file so you can reuse it. Here is a sample script:

    /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_TestTable
    (
    tableKey int NOT NULL,
    Description varchar(50) NOT NULL,
    TextData text NOT NULL
    )  ON [PRIMARY]
     TEXTIMAGE_ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.TestTable)
     EXEC('INSERT INTO dbo.Tmp_TestTable (tableKey, Description, TextData)
     SELECT tableKey, Description, TextData FROM dbo.TestTable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.TestTable
GO
EXECUTE sp_rename N'dbo.Tmp_TestTable', N'TestTable', 'OBJECT' 
GO
ALTER TABLE dbo.TestTable ADD CONSTRAINT
    PK_TestTable PRIMARY KEY CLUSTERED 
    (
    tableKey
    ) ON [PRIMARY]

GO
COMMIT

Answer 7

I think getting rid of the null values is the easist.

(as raz0rf1sh has said)

CREATE TABLE tmp1( col1 INT identity ( 1, 1 ),   col2 TEXT )    
GO  

INSERT  
INTO     tmp1  
SELECT   NULL   

GO 10  

SELECT   *  
FROM     tmp1  

UPDATE tmp1  
SET      col2  = ''  
WHERE    col2 IS NULL  

ALTER TABLE tmp1   
ALTER COLUMN col2 TEXT NOT NULL  

SELECT   *
FROM     tmp1  

DROP TABLE tmp1