How can you script the a full-text index creation of a table to a file?

You can use system stored procedures to create a catalogue or start indexing a table...

http://msdn.microsoft.com/en-us/library/ms189801.aspx - sp_fulltext_catalog
http://msdn.microsoft.com/en-us/library/ms187960.aspx - sp_fulltext_table

Sample below

CREATE TABLE dbo.Test (
ID int IDENTITY(1, 1) NOT NULL, 
TextCol varchar(8000),
CONSTRAINT PK_Test_ID PRIMARY KEY CLUSTERED (ID)
)
 
-- create a catalogue for this table.
EXEC dbo.sp_fulltext_catalog 'TestCatalogue', 'create'
EXEC dbo.sp_fulltext_table 'dbo.Test', 'create', 'TestCatalogue', 'PK_Test_ID'
 
DROP TABLE dbo.Test

Select allOpen in new window

In looking at your code example, where are telling the stored procedure what field you are creating the full-text index search on?  Since your table only has one field, does it just do it for all fields?  If I had the following 5 fields:  ID, NAME, DEPT, COST CENTER.

If I only wanted to create the full-text search ability on the NAME and DEPT fields, how would the call to the stored procedures look?

Looking back at the documentation, it seems you have to add the columns manually, then activate the table and populate the index.

Does the snippet below work for you?

CREATE TABLE dbo.Test (
ID int IDENTITY(1, 1) NOT NULL, 
TextCol varchar(8000),
CONSTRAINT PK_Test_ID PRIMARY KEY CLUSTERED (ID)
)
 
-- create a catalogue for this table.
EXEC dbo.sp_fulltext_catalog 'TestCatalogue', 'create'
EXEC dbo.sp_fulltext_table 'dbo.Test', 'create', 'TestCatalogue', 'PK_Test_ID'
EXEC dbo.sp_fulltext_column 'dbo.Test', 'TextCol', 'add'
EXEC dbo.sp_fulltext_table 'dbo.Test', 'activate', NULL, NULL
EXEC dbo.sp_fulltext_catalog 'TestCatalogue', 'start_full'
DROP TABLE dbo.Test

Select allOpen in new window

Sorry for my inexperience in this an other responses.

In your code I see you are both creating and dropping the table in the script.  Why would you do this.  If this is a table you would want to keep around then the DROP would not be needed.

In my situation, I've got scripts that will create the table and bulk insert the data into it.  And so since I'm working on an existing table, I would just need to contab the EXEC statements in my script to create the full-text index, correct?

Also, I see where the site you referred me to says that this stored procedure will not be supported in future releases of MS SQL.  Are you aware of any way to do this that would work both in SQL Server 2005 and beyond?

Thank you for your help.

I cliked on the link above and it appears to possibly be what I was looking for.   For all of my other scripts, I was able through the Managemnt Studio to right click and have it script out the create TSQL code for me, but I didn't see how you could do this through the Management studio for the full-text index.  Let me play with  the code on here and see if I can get it to work.  I'll let you know.

Thanks for  your help.

This did the trick!  Thank you so much for your help.  I've got several books on SQL Server 2005 and the CREATE FULLTEXT INDEX command is not mentioned.  Thank you for your help.  I now have a .bat file that will drop, create, insert and create the full-text index for my table so I can put it in a .bat (that includes an ftp to bring a fresh input file over) and refresh my table.  Thanks again for your help.