Checking for existence of a table that is already full-text indexe

Full-text indexing was manually set up to work on the development server.
Now, I need to write a SQL script to programmatically set up Full-text
indexing on the staging server and then on the production server.
The SQL script must be rerunnable. It should handle the scenario where
full-text indexing already exists or not, per table, as necessary.
The SQL script works on the first pass successfully (because the staging
server did not have full-text indexing).
EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
GO
The SQL script fails on the second pass on the staging server. Because it
tries to create the index that already exists.
So, I added a statement to drop the index before creating the index.
EXEC sp_fulltext_table 'tablename', 'drop'
GO
EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
GO
That works because it drops an existing index, then creates the index.
However, the drop statement will fail if an index doesn't exist.
So then, how do I check for the existence of a table index before dropping it?
IF EXISTS ("statement to check for existence of a table index")
BEGIN
EXEC sp_fulltext_table 'tablename', 'drop'
GO
END
EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
GO
I looked at sp_help_fulltext_tables, but it doesn't return TRUE/FALSE that I
could use in the if statement.
EXEC sp_help_fulltext_tables 'Catalog', 'tablename'
GO
Any suggestions?
MGBloomfield,
Yes, there are some good T-SQL code examples can be found & modified for
your purposes in the procedures in KB article: 240867 (Q240867) "INF: How to
Move, Copy, and Backup Full-Text Catalog Folders and Files" at:
http://support.microsoft.com/default...b;EN-US;240867
You might also find this code useful as well:
-- To Create/Remove the Existing Full-Text Table Index, Catalog
-- If Full-Text Index exists, DROP that Index,
-- If Full-Text Index does not exist, CREATE that Index.
use pubs
go
IF OBJECTPROPERTY ( object_id('pub_info'),'TableHasActiveFulltextIndex ') = 1
BEGIN
print 'Table pub_info is Full-Text Enabled, dropping Full-Text Index &
Catalog...'
EXEC sp_fulltext_table 'pub_info', 'drop'
EXEC sp_fulltext_catalog 'PubInfo', 'drop'
END
ELSE IF OBJECTPROPERTY (
object_id('pub_info'),'TableHasActiveFulltextIndex ') = 0
BEGIN
print 'Table pub_info is NOT Full-Text Enabled, creating FT Catalog,
Index & Activating...'
EXEC sp_fulltext_catalog 'PubInfo', 'create'
EXEC sp_fulltext_table 'pub_info', 'create', 'PubInfo', 'UPKCL_pubinfo'
EXEC sp_fulltext_column 'pub_info', 'pub_id', 'add'
EXEC sp_fulltext_column 'pub_info', 'pr_info', 'add'
EXEC sp_fulltext_table 'pub_info', 'activate'
END
Regards,
John
"MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
news:F80D1509-E8EF-4B4F-9011-BA44DF7EF91C@.microsoft.com...
> Full-text indexing was manually set up to work on the development server.
> Now, I need to write a SQL script to programmatically set up Full-text
> indexing on the staging server and then on the production server.
> The SQL script must be rerunnable. It should handle the scenario where
> full-text indexing already exists or not, per table, as necessary.
> The SQL script works on the first pass successfully (because the staging
> server did not have full-text indexing).
> EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
> GO
> The SQL script fails on the second pass on the staging server. Because it
> tries to create the index that already exists.
> So, I added a statement to drop the index before creating the index.
> EXEC sp_fulltext_table 'tablename', 'drop'
> GO
> EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
> GO
> That works because it drops an existing index, then creates the index.
> However, the drop statement will fail if an index doesn't exist.
> So then, how do I check for the existence of a table index before dropping
it?
> IF EXISTS ("statement to check for existence of a table index")
> BEGIN
> EXEC sp_fulltext_table 'tablename', 'drop'
> GO
> END
> EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
> GO
> I looked at sp_help_fulltext_tables, but it doesn't return TRUE/FALSE that
I
> could use in the if statement.
> EXEC sp_help_fulltext_tables 'Catalog', 'tablename'
> GO
> Any suggestions?
>