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?
>

Checking and Indexing

Hi,
Does running the SQL maintenance tools such as indexing and checking while
users are using the database cause problems?
Thanks
Any maintenance activity such as DBCC CHECKDB and DBREINDEX will have some
effect due to their resource intensive nature. But DBCC CHECKDB can be run
while users are accessing the system without stopping normal access to the
tables. The system may be slower due to the CPU usage of the command but it
will not prevent them altogether. DBCC DBREINDEX on the other hand will
stop any access to the table it is currently working on. From the time it
starts to reindex anything in that table it takes an exclusive table lock
and holds it until it finish's with that table.
Andrew J. Kelly SQL MVP
"Amir Marathonian" <AmirMarathonian@.discussions.microsoft.com> wrote in
message news:193BC0D7-1BF7-415B-8B74-1FBCBC4C649F@.microsoft.com...
> Hi,
> Does running the SQL maintenance tools such as indexing and checking while
> users are using the database cause problems?
> Thanks