Database field type conversion problem

sunray

Do alter table operations in the query manager? Enterprise Manager--b GUI graphical tools are not as efficient as command lines. Although it is easy to operate

德鲁伊

ALTER TABLE
Change the definition of the table by changing, adding, removing columns and constraints, or by enabling or disabling constraints and triggers.
ALTER COLUMN

Specifies to change the given column. If the compatibility level is 65 or less, ALTER COLUMN will not be allowed. For more information, see sp_dbcmptlevel.

The column to be changed cannot be:

Columns with data type text, image, ntext, or timestamp.

It is written in the online help, which means that the Alter Table method cannot change the text, image, ntext or timestamp column

德鲁伊

It can be modified like this. This is an example. Change the ee field in table1 from ntext to text.
The reference is as follows:

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
CREATE TABLE dbo.Tmp_Table1
(
ee text NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (ee)
SELECT ee FROM dbo.Table1 (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1','OBJECT'
GO
COMMIT