April 25, 2012

sp_help_fulltext_system_components (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_help_fulltext_system_components(nvarchar @component_type
, nvarchar @param)

MetaData:

 create procedure sys.sp_help_fulltext_system_components  
@component_type sysname = NULL, -- component type wordbreaker or filter
@param sysname = NULL -- wordbreaker lcid, or filter extension, or PH name, or fullpath
as
-- dump catalogs that used the component that specified
if ((@component_type is null) or
@component_type not in ('wordbreaker','filter', 'protocol handler', 'fullpath', 'all'))
begin
raiserror(15600,-1,-1,'sys.sp_help_fulltext_system_components')
return 1
end

declare @reg_components TABLE(componenttype sysname,
componentname sysname,
clsid uniqueidentifier,
fullpath nvarchar(256),
version nvarchar(30),
manufacturer sysname)

-- list all components registered in system
if (@component_type = 'all')
begin
-- @param must be null if we want to dump all components registered in system
if (@param is not null)
begin
raiserror(15600,-1,-1,'sys.sp_help_fulltext_system_components')
return 1
end

-- select componentname, clsid, fullpath, version
insert into @reg_components
select componenttype,
componentname,
clsid,
fullpath,
version,
manufacturer
from sys.fn_ftcatcomponents(1, 0, 0)
end

-- list all components registered in system with specified type
-- if @param is specified, we are looking for specific component
if (@component_type in ('wordbreaker','filter', 'protocol handler'))
begin
-- if @param is not null, we only dump registered components which has name @param
insert into @reg_components
select componenttype,
componentname,
clsid,
fullpath,
version,
manufacturer
from sys.fn_ftcatcomponents(1, 0, 0)
where componenttype = @component_type AND ( @param is null OR componentname = @param)
end

if (@component_type = 'fullpath')
begin
-- CHECK PERMISSIONS (must be serveradmin) --
if (is_srvrolemember('serveradmin') = 0)
begin
raiserror(15247,-1,-1)
return 1
end

-- @param must not be null if we want to search based on fullpath
if (@param is null)
begin
raiserror(15600,-1,-1,'sys.sp_help_fulltext_system_components')
return 1
end

insert into @reg_components
select componenttype,
componentname,
clsid,
fullpath,
version,
manufacturer
from sys.fn_ftcatcomponents(1, 0, 0)
where fullpath = @param
end

-- dump registered components to user
select componenttype,
componentname,
clsid,
(case when is_srvrolemember('serveradmin') = 1 then fullpath else NULL end) AS [fullpath],
version,
manufacturer from @reg_components


-- if we find any registered components meets the requirment,
-- and if we search for a specific type of component, we need to dump all catalogs that used them.
if (@param is not null and @@ROWCOUNT > 0)
begin
select distinct smf.database_id as dbid, ftcomp.catalogid as ftcatid
from sys.master_files as smf cross apply sys.fn_ftcatcomponents(0, smf.database_id, smf.file_id) as ftcomp
join @reg_components as regcomp on (ftcomp.clsid = regcomp.clsid AND
ftcomp.componenttype collate catalog_default = regcomp.componenttype collate catalog_default)
where smf.type = 4 -- is_fulltext_catalog
end

No comments:

Post a Comment

Total Pageviews