CODEWITHSUNDEEP
sqlsql-serverfull-text-searchaltercatalog
Gad D Lord
Gad D Lord

Reputation: 6772

How to ALTER FULLTEXT CATALOG from dynamically generated script for all databases on a server

How can I alter full text catalog by its Database.Owner.Name full catalog name?

declare @db_name sysname;
declare db cursor for 
select name from sys.Databases where name like 'myprefix%'
open db
fetch next from db into @db_name
while @@fetch_status = 0
begin
  set @sql = 'ALTER FULLTEXT CATALOG [' +  @db_name + '].dbo.IndexedContent REBUILD'
  exec sp_executesql @sql   
  fetch next from db into @db_name
end
close db;
deallocate db;

But SQL Server complains about 

Incorrect syntax near '.'

The documentation at http://msdn.microsoft.com/en-us/library/ms176095.aspx does not explicitly specify if catalog_name can be fully qualified.

Upvotes: 0

Views: 976

Answers (1)

Pondlife
Pondlife

Reputation: 16260

The documentation says that the catalog name is a single element i.e. no schema or database prefix (unlike the syntax for CREATE TABLE, for example). Therefore you probably need to switch execution context to the correct database first:

set @sql = 'USE ' +  QUOTENAME(@db_name) + '; ALTER FULLTEXT CATALOG IndexedContent REBUILD;'

Upvotes: 1

Related Questions