Saturday, February 25, 2012

Alter a fulltext index column from size 2000 to max

Thanks in advance for the help, I am a bit of a newbie.

How do I alter a column from varchar (2000) to varchar (max) when it is a fulltext indexed column. Keep in mind that this script will be deployed so the database name may change.

thnx

You can alter the data type only if there are no dependencies (indexes, constraints, references, computed column, partitioned etc) with few exceptions. To alter a column enabled for full-text index, you need to drop the fulltext index, alter the column and recreate the fulltext index.

|||

Like this:

IF EXISTS (SELECT * FROM [dbo].[sysobjects]

WHERE ID = object_id(N'[dbo].tblNews') AND

OBJECTPROPERTY(id, N'tblNews') = 1)

DROP FULLTEXT INDEX ON tblNews;

GO

ALTER TABLE tblNews ALTER COLUMN whatsNew VARCHAR(max) null;

GO

--HOW DO I RECREATE THE FULL-TEXT INDEX?

--DOES IT COME FROM THE CATALOGUE?

GO

|||Yep.

Then

CREATE FULLTEXT INDEX ON tblNews(whatsNew) KEY INDEX <unique index on tblNews>

No comments:

Post a Comment