April 25, 2012

sp_help_fulltext_tables_cursor (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_tables_cursor(nvarchar @fulltext_catalog_name
, nvarchar @table_name)

MetaData:

 create procedure sys.sp_help_fulltext_tables_cursor  
@cursor_return CURSOR VARYING OUTPUT,
@fulltext_catalog_name sysname = NULL, -- full-text catalog name
@table_name nvarchar(517) = NULL -- table name
as
-- FULLTEXT MUST BE ACTIVE IN DATABASE --
if DatabasePropertyEx(db_name(), 'IsFulltextEnabled') = 0
begin
raiserror(15601,-1,-1)
return 1
end

-- get current db name
declare @curdbname sysname
select @curdbname = db_name()

-- CATALOG MUST EXIST IF SPECIFIED --
declare @ftcatid smallint
if @fulltext_catalog_name is not null
begin
select @ftcatid = fulltext_catalog_id from sys.fulltext_catalogs where name = @fulltext_catalog_name
if @ftcatid is null
begin
declare @curdbnamelen int
select @curdbnamelen = LEN(@curdbname)
raiserror(7641,-1,-1,@fulltext_catalog_name, @curdbnamelen, @curdbname)
return 1
end
end

if @table_name is not null
begin
-- VALIDATE TABLE NAME --
-- (1) Must exist in current database
declare @objid int
select @objid = object_id(@table_name, 'local')
if @objid is null
begin
raiserror(15009,-1,-1 ,@table_name, @curdbname)
return 1
end
-- (2) Must be a user table or indexed view (and not a temp table)
if (ObjectProperty(@objid, 'IsUserTable') = 0 AND (ObjectProperty(@objid, 'IsView') = 0 OR ObjectProperty(@objid, 'IsIndexed') = 0 )) OR
substring(parsename(@table_name,1),1,1) = '#'
begin
raiserror(15218,-1,-1 ,@table_name)
return 1
end
end

-- We are using schema owner as table owner now. Check with garish for their decision on using schema owner
-- in place of table owner for backward compatibility issue.
set @cursor_return = CURSOR LOCAL SCROLL STATIC FOR
select ssche.name as TABLE_OWNER,
sobj.name as TABLE_NAME,
sidx.name as FULLTEXT_KEY_INDEX_NAME,
sidxcol.column_id as FULLTEXT_KEY_COLID,
sftidx.is_enabled as FULLTEXT_INDEX_ACTIVE,
scat.name as FULLTEXT_CATALOG_NAME
from sys.objects as sobj WITH(NOLOCK) -- the nolock is to not block while crawl or master merge is creating fragments
join sys.fulltext_indexes as sftidx on (sobj.object_id = sftidx.object_id)
join sys.indexes as sidx on(sftidx.unique_index_id = sidx.index_id and sftidx.object_id = sidx.object_id)
join sys.schemas as ssche on (sobj.schema_id = ssche.schema_id)
join sys.index_columns as sidxcol on (sftidx.object_id = sidxcol.object_id and sftidx.unique_index_id = sidxcol.index_id)
left outer join sys.fulltext_catalogs as scat on (scat.fulltext_catalog_id = sftidx.fulltext_catalog_id)
where(
@fulltext_catalog_name is null or
sftidx.fulltext_catalog_id = @ftcatid
) and
(
@table_name is null or
sobj.object_id = @objid
)

order by TABLE_OWNER, TABLE_NAME

open @cursor_return

-- SUCCESS --
return 0 -- sp_help_fulltext_tables_cursor

No comments:

Post a Comment

Total Pageviews