One of the things that I have found disappointing about SQL 2005 Management Studio apart from the severe slowness of the GUI compared to 2k is the fact that there is no option (that I can find) that will allow you to generate the necessary scripts for fulltext indexes. If you select a fulltext catalog under the storage section and choose to script a create you will get the following:
CREATE FULLTEXT CATALOG [System_Help]
IN PATH N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData'
WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION [dbo]
Which is just the script to rebuild the catalog and not the actual indexes within the catalog.
Obviously this is not very useful if you are wanting to quickly re-create the indexes along with the catalog and hopefully they will remedy this in SQL 2008. However until that day comes you can either rebuild the indexes by hand using the GUI or use a script like the one I have created which will:
- Script a CREATE FULLTEXT CATALOG statement for the catalog.
- Loop through each fulltext index within the catalog and script the necessary CREATE FULLTEXT INDEX statements.
An example of the SQL generated by my script is below. Its a catalog I created purely for testing that contains two indexes.
CREATE FULLTEXT CATALOG SystemHelp
WITH ACCENT_SENSITIVITY = OFF
GO
CREATE FULLTEXT INDEX ON [dbo].[tbl_CLIENTS] (
CompanyName Language 1033
,description Language 1033
,email Language 1033
,address1 Language 1033
)
KEY INDEX PK_tbl_CLIENTS
ON SystemHelp
WITH CHANGE_TRACKING AUTO
GO
CREATE FULLTEXT INDEX ON [dbo].[tbl_SYSTEM_HELP] (
Title Language 1033
,Article Language 1033
,Tags Language 1033
,PageName Language 1033
,RelatedSection Language 1033
)
KEY INDEX PK_tbl_SYSTEM_HELP
ON SystemHelp
WITH CHANGE_TRACKING AUTO
GO
Obviously you can take my script modify and extend it to incorporate any missing settings that you require as I have created it purely for moving my own existing indexes. However as the script stands its another good example of using the system views to solve common DBA problems and I am still scratching my head about why you can script almost every other object in SQL 2005 apart from the fulltext indexes. Maybe there is a hidden button I have not found yet! If anyone knows where it is please show me.
Posts
