开发者

Sql Server 2008 - How to query for status of fulltext catalogs on all user databases?

I have several databases in a Sql Server 2008 R2 instance. Some of those databases have a full-text enabled table. The name of the full-text table is equal for all databases, but the databases have different names and they are created on demand (I never know what databases exists and what does not).

The thing is: I need to query all catalogs in all databases to check if a population is done, but I have no idea how many database开发者_C百科s I have (of course I know, but they are created on demand as I said). The script must query all databases and check if a population is done in a table (which the name I know because it never changes besides the name of the database that does change)

I have seen many people using things like:

sys.fulltext_catalogs

But it does not work if i am using the master database for example.

Any ideas?


Edit: Here is more complete code with a cursor, a full list of databases (even those without catalogs), and the right catalog view name:

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += ' UNION ALL 
    SELECT [name] = ''' + QUOTENAME(name) + ''', 
    catalog_name = name COLLATE Latin1_General_CI_AI,
    is_importing
  FROM ' + QUOTENAME(name) + '.sys.fulltext_catalogs'
  FROM sys.databases WHERE database_id > 4;

SET @sql = 'SELECT [database] = d.name, 
    s.catalog_name,
    s.is_importing
FROM sys.databases AS d 
LEFT OUTER JOIN (' + STUFF(@sql, 1, 10, '') + ') AS s 
ON QUOTENAME(d.name) = s.name
WHERE d.database_id > 4;';

CREATE TABLE #temp(db SYSNAME, catalog_name NVARCHAR(255), is_importing BIT);

INSERT #temp EXEC sp_executesql @sql;

DECLARE @db SYSNAME, @catalog_name NVARCHAR(255), @is_importing BIT;

DECLARE c CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR SELECT db, catalog_name, is_importing FROM #temp;

OPEN c;

FETCH NEXT FROM c INTO @db, @catalog_name, @is_importing;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @catalog_name IS NULL
    BEGIN
        PRINT 'No catalogs for ' + @db;
    END
    ELSE
    BEGIN
        IF @is_importing = 1
        BEGIN
            PRINT 'Do something to ' + @db 
                + '(importing)';
        END
        ELSE
        BEGIN
            PRINT @db + ' is not importing.';
        END
    END
    FETCH NEXT FROM c INTO @db, @catalog_name, @is_importing;
END

CLOSE c;
DEALLOCATE c;

DROP TABLE #temp;


This gives you a complete list of used catalogs.

CREATE TABLE #info (
  databasename VARCHAR(128)
, [Fulltext Catalog Name] VARCHAR(128));

SET NOCOUNT ON;
INSERT INTO #info

EXEC sp_MSforeachdb 'use ? 
SELECT ''?''
     , name 
FROM sys.fulltext_catalogs;'

SELECT * FROM #info 


-- get rid of temp table 
DROP TABLE #info;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜