如何更改SQL Server中数据库表上的TEXT列？

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:

在SQL Server数据库中,我有一个表,其中包含一个TEXT字段,该字段设置为允许NULL。我需要将其更改为不允许NULL。我可以通过企业管理器做到这一点没有问题,但是当我尝试运行以下脚本时,改变表dbo。[EventLog]更改列[消息]文本不为空,我收到错误:

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

无法更改列'ErrorMessage',因为它是'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?

阅读SQL Books Online确实显示您不允许在TEXT字段上执行ALTER COLUMN。我确实需要能够通过脚本执行此操作,而不是在Enterprise Manager中手动执行此操作。那么在脚本中执行此操作的选项有哪些?

6 个解决方案

#1

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:

您可以使用Enterprise Manager创建脚本。右键单击EM中的表,然后选择“设计”。取消选中Text字段的Allow Nulls列。而不是点击常规保存图标(软盘),单击一个看起来像一个小软盘的金色滚动的图标或只是从菜单中执行表设计器>生成更改脚本。将脚本保存到文件中以便重复使用。这是一个示例脚本:

    /* 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

#2

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

创建一个新字段。复制数据。放下旧场。重命名新字段。

#3

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

我认为摆脱空值是容易的。

(as raz0rf1sh has said)

(正如raz0rf1sh所说)

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  

#4

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.

在我的脑海中,我要说你需要创建一个与现有表具有相同结构的新表,但是将text列设置为not null,然后运行查询以将记录从一个移动到另一个。

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.

如果其他人对确切的TSQL语法有更好的把握,请关注这个答案,请随意。

#5

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.

我会用NULL值更新所有列并将其设置为空字符串,例如''。然后,您应该能够毫无问题地运行ALTER TABLE脚本。比创建新列少得多的工作。

#6

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

尝试从Enterprise Manager中生成更改脚本,以查看它是如何在那里完成的