April 22, 2012

sp_fulltext_column (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_fulltext_column(nvarchar @tabname
, nvarchar @colname
, varchar @action
, int @language
, nvarchar @type_colname)

MetaData:

 create proc sys.sp_fulltext_column  
@tabname nvarchar(517), -- table name
@colname sysname, -- column name
@action varchar(20), -- add | drop
@language int = null, -- LCID of data in the column
@type_colname sysname = null -- column name, valid if colname is img

as
declare @execstring nvarchar (4000)
declare @newtabname nvarchar (1035)

set nocount on

-- sp_fulltext_column will run under read committed isolation level --
set transaction isolation level READ COMMITTED

if (db_name() in ('master','tempdb','model'))
begin
raiserror(9966, -1, -1)
return 1
end

-- add quote to table name. fn_quotefourpartname can add quote to four part name --
select @newtabname=sys.fn_quotefourpartname(@tabname,N'[')
if @newtabname is null
begin
raiserror(15600,-1,-1,'sys.sp_fulltext_column')
return 1
end

-- VALIDATE TABLE NAME --
-- (1) Must exist in current database
declare @objid int
select @objid = object_id(@newtabname, 'local')
if @objid is null
begin
declare @curdbname sysname
select @curdbname = db_name()
raiserror(15009,-1,-1 ,@tabname, @curdbname)
return 1
end

-- CHECK PERMISSION ON TABLE --
if (is_member('db_owner') = 0) AND (is_member('db_ddladmin') = 0)
AND (is_member(user_name(ObjectProperty(@objid, 'ownerid'))) = 0)
begin
raiserror(15247,-1,-1)
return 1
end

-- VALIDATE PARAMS --
if @colname is null or len(@colname) = 0 or @action is null or @action not in ('add','drop')
begin
raiserror(15600,-1,-1,'sys.sp_fulltext_column')
return 1
end

if @language is not null AND @language < 0
begin
raiserror(15600,-1,-1,'sys.sp_fulltext_column')
return 1
end

-- DISALLOW USER TRANSACTION --
set implicit_transactions off
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sys.sp_fulltext_column')
return 1
end

if @action = 'add'
begin
if exists ( select ftcol.object_id from sys.fulltext_index_columns as ftcol join sys.columns as col
on (ftcol.object_id = col.object_id and ftcol.column_id = col.column_id)
where col.name = @colname and col.object_id = object_id(@newtabname))
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' DROP ('
+ quotename( @colname, '[')
+ ' ) '

if ObjectProperty(object_id(@newtabname, 'local'), 'TableFulltextChangeTrackingOn') = 0
begin
select @execstring = @execstring +' WITH NO POPULATION '
end
EXEC (@execstring)
end

select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' ADD ('
+ quotename( @colname, '[')
+ CASE
when @type_colname is null or len(@type_colname) = 0 then ''
else ' TYPE COLUMN '+ quotename( @type_colname, '[')
END
+ CASE
when @language is null then ''
else ' LANGUAGE '+ cast(@language as varchar)
END
+ ' ) '

if ObjectProperty(object_id(@newtabname, 'local'), 'TableFulltextChangeTrackingOn') = 0
begin
select @execstring = @execstring +' WITH NO POPULATION '
end
EXEC (@execstring)
end
else
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' DROP ( '
+ quotename( @colname, '[')
+ ' ) '

if ObjectProperty(object_id(@newtabname, 'local'), 'TableFulltextChangeTrackingOn') = 0
begin
select @execstring = @execstring +' WITH NO POPULATION '
end

EXEC (@execstring)
end

-- SUCCESS --
return 0

sp_filestream_force_garbage_collection (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_filestream_force_garbage_collection(nvarchar @dbname
, nvarchar @filename)

MetaData:

   
create procedure sys.sp_filestream_force_garbage_collection
(
@dbname sysname = NULL, -- name of the database where to collect
@filename sysname = NULL -- FILESTREAM file container name
)
as
begin
set nocount on

set @dbname = ISNULL (@dbname, DB_NAME ())

declare @returncode int

EXEC @returncode = sys.sp_filestream_force_garbage_collection_internal @dbname, @filename

return @returncode
end

sp_foreign_keys_rowset (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_foreign_keys_rowset(nvarchar @pk_table_name
, nvarchar @pk_table_schema
, nvarchar @foreignkey_tab_name
, nvarchar @foreignkey_tab_schema
, nvarchar @foreignkey_tab_catalog)

MetaData:

   
create procedure sys.sp_foreign_keys_rowset
(
@pk_table_name sysname,
@pk_table_schema sysname = null,
@foreignkey_tab_name sysname = null,
@foreignkey_tab_schema sysname = null,
@foreignkey_tab_catalog sysname = null
)
as
select
-- PK_TABLE_CATALOG = db_name(r.rkeydbid),
PK_TABLE_CATALOG = db_name(),
PK_TABLE_SCHEMA = schema_name(o1.schema_id),
PK_TABLE_NAME = o1.name,
PK_COLUMN_NAME = c1.name,
PK_COLUMN_GUID = convert(uniqueidentifier,null),
PK_COLUMN_PROPID = convert(int,null),
-- FK_TABLE_CATALOG = db_name(r.fkeydbid),
FK_TABLE_CATALOG = db_name(),
FK_TABLE_SCHEMA = schema_name(o2.schema_id),
FK_TABLE_NAME = o2.name,
FK_COLUMN_NAME = c2.name,
FK_COLUMN_GUID = convert(uniqueidentifier,null),
FK_COLUMN_PROPID = convert(int,null),
ORDINAL = convert(int,k.constraint_column_id),
-- UPDATE_RULE = CASE ObjectProperty(r.constid, 'CnstIsUpdateCascade')
UPDATE_RULE = CASE r.update_referential_action
WHEN 1 THEN N'CASCADE'
WHEN 2 THEN N'SET NULL'
WHEN 3 THEN N'SET DEFAULT'
ELSE N'NO ACTION'
END,
-- DELETE_RULE = CASE ObjectProperty(r.constid, 'CnstIsDeleteCascade')
DELETE_RULE = CASE r.delete_referential_action
WHEN 1 THEN N'CASCADE'
WHEN 2 THEN N'SET NULL'
WHEN 3 THEN N'SET DEFAULT'
ELSE N'NO ACTION'
END,
PK_NAME = i.name,
-- FK_NAME = object_name(r.constid),
FK_NAME = object_name(r.object_id),
DEFERRABILITY = convert(smallint, 3) -- DBPROPVAL_DF_NOT_DEFERRABLE
from
sys.all_objects o1, -- ISSUE - PERF - do inner joins here instead of old join in where clause !!!
sys.all_objects o2, -- ISSUE - PERF - do inner joins here instead of old join in where clause !!!
sys.all_columns c1,
sys.all_columns c2,

-- sysreferences r,
sys.foreign_keys r inner join
sys.foreign_key_columns k on (k.constraint_object_id = r.object_id) inner join
sys.indexes i on (r.referenced_object_id = i.object_id and r.key_index_id = i.index_id)
where
(@foreignkey_tab_catalog is null or @foreignkey_tab_catalog = db_name())
and ( @pk_table_name = o1.name) -- ISSUE - PERF - Do check against object_id, not against the names !!!
and (@pk_table_schema is null or @pk_table_schema = schema_name(o1.schema_id)) -- ISSUE - PERF - Do check against object_id, not against the names !!!
and (@foreignkey_tab_name is null or @foreignkey_tab_name = o2.name) -- ISSUE - PERF - Do check against object_id, not against the names !!!
and (@foreignkey_tab_schema is null or @foreignkey_tab_schema = schema_name(o2.schema_id)) -- ISSUE - PERF - Do check against object_id, not against the names !!!
and
o1.object_id = r.referenced_object_id and
o1.object_id = c1.object_id and
c1.column_id = k.referenced_column_id and
o2.object_id = r.parent_object_id and
o2.object_id = c2.object_id and
c2.column_id = k.parent_column_id
order by 8,9,2,3,13

sp_enumdsn (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_enumdsn()

MetaData:

   
create procedure sys.sp_enumdsn
AS

if 1 <> is_srvrolemember('sysadmin')
begin
raiserror(15247,-1,-1)
return 1
end

SET NOCOUNT ON

DECLARE @distributor sysname
DECLARE @distproc nvarchar (300)
DECLARE @retcode int

DECLARE @dsotype_odbc int
DECLARE @dsotype_oledb int

select @dsotype_odbc = 1
select @dsotype_oledb = 3

--
-- Get distribution server information for remote RPC
-- subscription calls.
--

EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @rpcsrvname = @distributor OUTPUT
IF @@error <> 0 OR @retcode <> 0 or @distributor is null
BEGIN
RAISERROR (14071, 16, -1)
RETURN (1)
END

create table #datasourcestemptable (DataSourceName sysname collate database_default not null, Description nvarchar(255) collate database_default null, DataSourceType int null, ProviderName nvarchar(255) collate database_default null)

--
-- Call xp_enumdsn
--
SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.master.sys.xp_enumdsn'
insert into #datasourcestemptable(DataSourceName, Description) EXEC @retcode = @distproc
IF @@error <> 0
BEGIN
RAISERROR (14071, 16, -1)
RETURN (1)
END

update #datasourcestemptable set DataSourceType = @dsotype_odbc where DataSourceType is null
IF @@error <> 0
BEGIN
RETURN (1)
END

--
-- Call sp_enumoledbdatasources
--
SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.master.sys.sp_enumoledbdatasources'
insert into #datasourcestemptable(DataSourceName, Description, ProviderName) EXEC @retcode = @distproc
IF @@error <> 0
BEGIN
RAISERROR (14071, 16, -1)
RETURN (1)
END

update #datasourcestemptable set DataSourceType = @dsotype_oledb where DataSourceType is null
IF @@error <> 0
BEGIN
RETURN (1)
END

select 'Data Source Name' = DataSourceName, Description, 'Type' = DataSourceType, 'Provider Name' = ProviderName
from #datasourcestemptable
order by 3, 1

drop table #datasourcestemptable

return (0)

sp_dsninfo (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_dsninfo(varchar @dsn
, varchar @infotype
, varchar @login
, varchar @password
, int @dso_type)

MetaData:

   
--
-- Name: sp_dsninfo
--
-- Descriptions:
--
-- Parameters: as defined in create statement
--
-- Returns: 0 - success
-- 1 - Otherwise
--
-- Security:
-- Requires Certificate signature for catalog access
--
create procedure sys.sp_dsninfo
-- xp_dsninfo does not support unicode
@dsn varchar(128),
@infotype varchar(128) = NULL,
@login varchar(128) = NULL,
@password varchar(128) = NULL,
@dso_type int = 1 -- 1 is ODBC, 3 OLEDB. --
AS

SET NOCOUNT ON

DECLARE @distributor sysname
DECLARE @distproc nvarchar (300)
DECLARE @retcode int
DECLARE @dsotype_odbc int
DECLARE @dsotype_oledb int

select @dsotype_odbc = 1
select @dsotype_oledb = 3

--
-- Security Check: require sysadmin
--
IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
BEGIN
RAISERROR(21089,16,-1)
RETURN (1)
END

--
-- Get distribution server information for remote RPC
-- subscription calls.
--

EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @rpcsrvname = @distributor OUTPUT
IF @@error <> 0 OR @retcode <> 0
BEGIN
RAISERROR (14071, 16, -1)
RETURN (1)
END

if (@dso_type = @dsotype_odbc)
begin
--
-- Call xp_dsninfo
--
SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.master.dbo.xp_dsninfo'
EXEC @retcode = @distproc @dsn, @infotype, @login, @password
IF @@error <> 0
BEGIN
RAISERROR (14071, 16, -1)
RETURN (1)
END
end
else if (@dso_type = @dsotype_oledb)
begin
--
-- Call sp_oledbinfo
--
EXEC @retcode = sys.sp_oledbinfo @dsn, @infotype, @login, @password
IF @@error <> 0
BEGIN
RAISERROR (14071, 16, -1)
RETURN (1)
END
end

sp_dropserver (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_dropserver(nvarchar @server
, char @droplogins)

MetaData:

 create procedure sys.sp_dropserver  
@server sysname, -- server name
@droplogins char(10) = NULL -- drop all related logins?
as
declare @ret int, @is_linked bit, @server_id int
select @droplogins = LOWER(@droplogins collate Latin1_General_CI_AS)

-- DISALLOW USER TRANSACTION
set implicit_transactions off
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sys.sp_dropserver')
return (1)
end

-- CHECK PERMISSIONS
if not (has_perms_by_name(null, null, 'alter any linked server') = 1)
begin
EXEC %%System().AuditEvent(ID = 1380209484, Success = 0, TargetLoginName = NULL, TargetUserName = NULL, Role = NULL, Object = NULL, Provider = NULL, Server = @server)
raiserror(15247,-1,-1)
return (1)
end
else
begin
EXEC %%System().AuditEvent(ID = 1380209484, Success = 1, TargetLoginName = NULL, TargetUserName = NULL, Role = NULL, Object = NULL, Provider = NULL, Server = @server)
end

-- VALIDATE OPTION
if @droplogins is not null AND @droplogins <> 'droplogins'
begin
raiserror(15600,-1,-1, 'sys.sp_dropserver')
return (1)
end

begin transaction

-- CHECK/LOCK SERVER NAME / GET SERVER ID
EXEC %%LinkedServer ( Name = @server ) . Lock ( Exclusive = 1 )
IF @@ERROR <> 0
begin
rollback tran
raiserror(15015,-1,-1,@server)
return (1)
end

-- CHECK @droplogins PARAMETER: No sysremotelogins OR sysoledbusers ROWS EXCEPT
-- THE DEFAULT OLEDB-MAPPING.
if @droplogins is null AND exists (
SELECT * FROM master.dbo.sysservers s WHERE s.srvname = @server AND
( exists (select * from master.dbo.sysremotelogins WHERE remoteserverid = s.srvid)
OR exists (select * from master.dbo.sysoledbusers WHERE rmtsrvid = s.srvid
AND NOT (loginsid IS NULL AND status = 1) )
) )
begin
rollback tran
raiserror(15190,-1,-1,@server)
return (1)
end

-- CHECK TO SEE IF THE SERVER IS USED BY REPLICATION.
if object_id('sp_MSrepl_check_server') is not null
begin
EXEC @ret = sp_MSrepl_check_server @server
if @ret <> 0 or @@error <> 0
begin
rollback tran
return 1
end
end

-- is this a linked server?
select @is_linked = is_linked, @server_id = server_id from sys.servers where name = @server

-- DROP THE SERVER (ALSO DROPS ALL REMOTE/LINKED LOGINS)
EXEC %%LinkedServer ( Name = @server ) . Remove()

-- Server_id = 0 means the local server name
if @server_id = 0
begin
-- EMDEventType(x_eet_Alter_Instance), EMDUniversalClass(x_eunc_Server), src major id, src minor id, src name
-- -1 means ignore target stuff, target major id, target minor id, target name,
-- # of parameters, 5 parameters
EXEC %%System().FireTrigger(ID = 214, ID = 100, ID = 0, ID = 0, Value = NULL,
ID = -1, ID = 0, ID = 0, Value = NULL,
ID = 2, Value = @server, Value = @droplogins, Value = NULL, Value = NULL, Value = NULL, Value = NULL, Value = NULL)
end
else if @is_linked = 1
begin
-- EMDEventType(x_eet_Drop_LinkedServer), EMDUniversalClass(x_eunc_LinkedServer), src major id, src minor id, src name
-- -1 means ignore target stuff, target major id, target minor id, target name,
-- # of parameters, 5 parameters
EXEC %%System().FireTrigger(ID = 262, ID = 102, ID = 0, ID = 0, Value = @server,
ID = -1, ID = 0, ID = 0, Value = NULL,
ID = 2, Value = @server, Value = @droplogins, Value = NULL, Value = NULL, Value = NULL, Value = NULL, Value = NULL)
end
else
begin
-- EMDEventType(x_eet_Drop_Remote_Server), EMDUniversalClass(x_eunc_Server), src major id, src minor id, src name
-- -1 means ignore target stuff, target major id, target minor id, target name,
-- # of parameters, 5 parameters
EXEC %%System().FireTrigger(ID = 240, ID = 100, ID = 0, ID = 0, Value = @server,
ID = -1, ID = 0, ID = 0, Value = NULL,
ID = 2, Value = @server, Value = @droplogins, Value = NULL, Value = NULL, Value = NULL, Value = NULL, Value = NULL)
end
-- SUCCESS
commit transaction
return (0) -- sp_dropserver

sp_fulltext_service (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_fulltext_service(nvarchar @action
, sql_variant @value)

MetaData:

 create proc sys.sp_fulltext_service  
@action nvarchar(100) = NULL,
@value sql_variant = NULL
as
set nocount on

-- sp_fulltext_service will run under read committed isolation level --
set transaction isolation level READ COMMITTED

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

-- DISALLOW USER TRANSACTION --
set implicit_transactions off
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sys.sp_fulltext_service')
return 1
end

-- initialize full-text configuration table --
declare @fulltext_configuration_table TABLE (
Name nvarchar(100) NOT NULL UNIQUE,
IsDeprecated smallint default 0,
IsVisible smallint default 0,
FNC_Name nvarchar(200),
HasValue smallint default 0,
Type nvarchar(30) default NULL,
NeedRangeCheck smallint default NULL,
minvalue bigint default NULL,
maxvalue bigint default NULL,
dflt_longlong bigint default NULL,
dflt_str nvarchar(1024) default NULL)

-- for resource_usage, we set both index and query performance level to the value. So when we read value we just return
-- index performance level. Index and query performance level are suppose to be same. However, user can directly set
-- index and query performance level and they can be different. Resouce usage is just a setting for backward compatibility.
-- name depre, visi,FNC_Name hasVal, type, rangechk, min, max, dfltl, dftlstr
insert into @fulltext_configuration_table values ('resource_usage', 1, 1, 'FTE_IndexingPerformanceLevel', 1, 'int', 1, 0, 0, NULL, NULL)
insert into @fulltext_configuration_table values ('clean_up', 1, 1, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL)
insert into @fulltext_configuration_table values ('connect_timeout', 1, 1, NULL, 1, 'int', 1, 0, 0, NULL, NULL)
insert into @fulltext_configuration_table values ('data_timeout', 1, 1, NULL, 1, 'int', 1, 0, 0, NULL, NULL)
insert into @fulltext_configuration_table values ('update_languages', 0, 1, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL)
insert into @fulltext_configuration_table values ('restart_all_fdhosts', 0, 0, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL)
insert into @fulltext_configuration_table values ('pause_indexing', 0, 1, 'pause_indexing', 1, 'int', 1, 0, 1, NULL, NULL)
-- The following are config values
insert into @fulltext_configuration_table values ('load_os_resources', 0, 1, 'FTE_LoadOSResources', 1, 'int', 1, 0, 1, 0, NULL)
insert into @fulltext_configuration_table values ('verify_signature', 0, 1, 'FTE_VerifySignature', 1, 'int', 1, 0, 1, 1, NULL)
insert into @fulltext_configuration_table values ('aggressive_break', 0, 0, 'FTE_AggressiveBreak', 1, 'int', 1, 0, 1, 1, NULL)
insert into @fulltext_configuration_table values ('ism_size', 0, 0, 'IFTS_ISMSize', 1, 'int', 1, 1, 16, 0, NULL)
insert into @fulltext_configuration_table values ('batch_size', 0, 0, 'IFTS_BatchSize', 1, 'int', 1, 100, 10000, 0, NULL)
insert into @fulltext_configuration_table values ('outstanding_batches', 0, 0, 'IFTS_OutstandingBatches', 1, 'int', 1, 1, 100, 5, NULL)
insert into @fulltext_configuration_table values ('outstanding_isms', 0, 0, 'IFTS_OutstandingISMs', 1, 'int', 1, 1, 100, 5, NULL)
insert into @fulltext_configuration_table values ('auto_crawl_end_fragments_merge', 0, 0, 'IFTS_AutoCrawlEndMergeFragments', 1, 'int', 1, 2, 50, 5, NULL)
insert into @fulltext_configuration_table values ('fdhost_tracing', 0, 0, 'IFTS_FDHostTracing', 1, 'int', 1, 0, 1, 0, NULL)
insert into @fulltext_configuration_table values ('fdhost_tracing_numberoflogs',0, 0, 'FTE_RetailTracingNumberOfLogs',1, 'int', 1, 1, 500, 10, NULL)
insert into @fulltext_configuration_table values ('fdhost_tracing_tag', 0, 0, 'IFTS_FDHostTracingTag', 1, 'nvarchar', NULL, NULL, NULL, NULL, N'')
insert into @fulltext_configuration_table values ('fdhost_dump', 0, 0, 'IFTS_FDHostDump', 1, 'int', 1, 0, 1, 1, NULL)
insert into @fulltext_configuration_table values ('processor_affinity', 0, 0, 'FTE_ProcessorAffinity', 1, 'int', NULL, NULL, NULL, 0, NULL)
insert into @fulltext_configuration_table values ('crawl_ranges', 0, 0, 'IFTS_NumRanges', 1, 'int', 1, 0, 256, 0, NULL)
insert into @fulltext_configuration_table values ('master_merge_dop', 0, 0, 'IFTS_PmmDop', 1, 'int', 1, 0, 256, 0, NULL)
insert into @fulltext_configuration_table values ('ft_timeout', 0, 0, 'IFTS_FTTimeout', 1, 'int', 1, 10000, 6000000,60000, NULL)
insert into @fulltext_configuration_table values ('max_singledoc_retries', 0, 0, 'IFTS_MaxSingleDocRetries', 1, 'int', 1, 0, 10, 1, NULL)
insert into @fulltext_configuration_table values ('upgrade_option', 0, 1, 'IFTS_CatalogUpgradeOptions', 1, 'int', 1, 0, 2, 2, NULL)
insert into @fulltext_configuration_table values ('fdhost_dump_flags', 0, 0, 'FTE_SQLDumperFlags', 1, 'nvarchar', NULL, NULL, NULL, NULL, N'0x820')
IF SERVERPROPERTY('IsNonGolden') IS NOT NULL
begin
insert into @fulltext_configuration_table values ('mm_partition_count', 0, 0, 'FTE_SQLMMPartitionCount', 1, 'int', 1, 0, 1000, 1000, NULL)
insert into @fulltext_configuration_table values ('merge_range_size_in_bytes', 0, 0, 'FTE_SQLMergeRangeSizeInBytes', 1, 'int', 1, 0, 100 * 1024 * 1024, 100 * 1024 * 1024, NULL)
insert into @fulltext_configuration_table values ('costing_factor', 0, 0, 'IFTS_Costingfactor', 1, 'int', 1, 1, 1000000, 1000, NULL)
end

-- if action is null, we print out all configuration options that are available to user --
if @action is null
begin
select Name as 'configuration name',
CASE IsDeprecated
WHEN 1 THEN 'Yes'
ELSE 'No'
end as 'is deprecated',
Type as 'configuration value type',
minvalue as 'minimum', maxvalue as 'maximum'
from @fulltext_configuration_table
where IsVisible = 1
order by Name
return 0
end

-- utility: list all sp_fullext_service options
if @action = 'show_all'
begin
select Name as 'configuration name',
CASE IsDeprecated
WHEN 1 THEN 'Yes'
ELSE 'No'
end as 'is deprecated',
Type as 'configuration value type',
minvalue as 'minimum', maxvalue as 'maximum'
from @fulltext_configuration_table
order by Name
return 0
end

declare @Name nvarchar(100)
declare @IsDeprecated smallint
declare @FNC_Name nvarchar(200)
declare @HasValue smallint
declare @Type nvarchar(30)
declare @NeedRangeCheck smallint
declare @minvalue bigint
declare @maxvalue bigint
declare @dfltlonglong bigint
declare @dfltstr nvarchar(1024)
declare @sval nvarchar(1024)


-- find configuration option from the table --
select @Name = Name,
@IsDeprecated = IsDeprecated,
@FNC_Name = FNC_Name,
@HasValue = HasValue,
@Type = Type,
@NeedRangeCheck = NeedRangeCheck,
@minvalue = minvalue,
@maxvalue = maxvalue,
@dfltlonglong = dflt_longlong,
@dfltstr = dflt_str
from @fulltext_configuration_table where Name = @action

-- Check if configuration option is exposed to user or Yukon Style options for mssearch internal use (no longer supported)
if (@Name is null)
begin

-- Yukon Style options for mssearch internal use (MSSearch only) options are not supported anymore
-- It has been decided that we throw an error for most of the MSSearch only options (some options will still be supported)
-- These options are not documented and has not been advertised to general public or CSS.
-- The other option is to make it a no-op for options we no longer support but it was decided (Jingweil and deepakp)
-- that we throw an error for the reasons explained above.
--
raiserror(15600,-1,-1,'sys.sp_fulltext_service')
return 1
end

-- if value is null, we print the option value --
if (@value is null)
begin
-- if the option doesn't have value, we will just run the command
if (@HasValue = 0)
begin
-- If it is deprecated, we will not do anything --
if (@IsDeprecated = 1)
begin
-- 21 logs the usage of the deprecated action and increments
-- the appropriate perf counter for this deprecated feature
--
DBCC CALLFULLTEXT(21, @Name)
return 0
end

-- DBCC CALLFULLTEXT (18 ) --
if @action = 'update_languages'
begin
DBCC CALLFULLTEXT (18 ) --
end
else if @action = 'restart_all_fdhosts'
begin
DBCC CALLFULLTEXT (22 )
end
else
begin
-- current only update_languages is non-value option
raiserror(15600,-1,-1,'sys.sp_fulltext_service')
return 1
end
end
else
begin
-- If it is deprecated, we will print 0 as value --
if (@IsDeprecated = 1)
begin
select @Name as 'Configuration Name', 0 as 'Configuration Value'
-- 21 logs the usage of the deprecated action and increments
-- the appropriate perf counter for this deprecated feature
--
DBCC CALLFULLTEXT(21, @Name)
return 0
end

-- print the configuration name and value
DBCC CALLFULLTEXT(2, @FNC_Name, @Name)
end
if @@error <> 0
return 1
end
else
begin
if (@HasValue = 0)
begin
-- try to set an option which don't take a value --
-- invalid parameter, raise error here --
raiserror(15600,-1,-1,'sys.sp_fulltext_service')
return 1
end
else
begin
-- If it is deprecated, we will not do anything --
if (@IsDeprecated = 1)
begin
-- 21 logs the usage of the deprecated action and increments
-- the appropriate perf counter for this deprecated feature
--
DBCC CALLFULLTEXT(21, @Name)
return 0
end

select @sval = convert(nvarchar(1024), @value)
if @@error <> 0
return 1

if (@Type = 'int')
begin
-- convert to int value first --
declare @i bigint
if ltrim(rtrim(lower(@sval))) = N'default'
select @i = @dfltlonglong
else
begin
select @i = convert(bigint, @sval)
if @@error <> 0
return 1
if @i = -1
select @i = @dfltlonglong
end

-- check if the value is in the range --
if ((@NeedRangeCheck = 1) and ( @i is null or (not (@i between @minvalue and @maxvalue) and @i <> @dfltlonglong)))
begin
raiserror(15600,-1,-1,'sys.sp_fulltext_service')
return 1
end
select @value = @i
end
else
begin
if ltrim(rtrim(lower(@sval))) = N'default'
select @value = @dfltstr
else
select @value = @sval
end

if @action = 'pause_indexing'
begin
DBCC CALLFULLTEXT ( 19, @value ) --
end
else
begin
DBCC CALLFULLTEXT ( 1, @FNC_Name, @value) -- Set config value
end
if @@error <> 0
return 1
end
end

-- SUCCESS --
return 0 -- sp_fulltext_service

sp_fulltext_table (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_fulltext_table(nvarchar @tabname
, varchar @action
, nvarchar @ftcat
, nvarchar @keyname)

MetaData:

 create proc sys.sp_fulltext_table  
@tabname nvarchar(517),
@action varchar(50),
@ftcat sysname = NULL, -- create: catalog name
@keyname sysname = NULL -- create: name of unique index
as

declare @execstring nvarchar (4000)
declare @newtabname nvarchar (1035)

set nocount on

-- sp_fulltext_table will run under read committed isolation level --
set transaction isolation level READ COMMITTED

if (db_name() in ('master','tempdb','model'))
begin
raiserror(9966, -1, -1)
return 1
end

-- add quote to table name. fn_quotefourpartname can add quote to four part name --
select @newtabname=sys.fn_quotefourpartname(@tabname,N'[')

if @newtabname is null
begin
raiserror(15600,-1,-1,'sys.sp_fulltext_table')
return 1
end

-- VALIDATE TABLE NAME --
-- (1) Must exist in current database
declare @objid int
select @objid = object_id(@newtabname, 'local')
if @objid is null
begin
declare @curdbname sysname
select @curdbname = db_name()
raiserror(15009,-1,-1 ,@tabname, @curdbname)
return 1
end

-- CHECK PERMISSION ON TABLE --
if (is_member('db_owner') = 0) AND (is_member('db_ddladmin') = 0)
AND (is_member(user_name(ObjectProperty(@objid, 'ownerid'))) = 0)
begin
raiserror(15247,-1,-1)
return 1
end

-- VALIDATE PARAMS --
if @action is null
OR @action not in ('create','drop','activate','deactivate',
'start_change_tracking', 'stop_change_tracking',
'start_background_updateindex', 'stop_background_updateindex',
'update_index', 'start_full', 'start_incremental', 'stop')
OR (@action not in ('create') and (@ftcat is not null or @keyname is not null))
OR (@action in ('create') and (@ftcat is null or len(@ftcat) = 0 or @keyname is null or len(@keyname) = 0))
begin
raiserror(15600,-1,-1,'sys.sp_fulltext_table')
return 1
end

-- DISALLOW USER TRANSACTION --
set implicit_transactions off
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sys.sp_fulltext_table')
return 1
end


if @action = 'create'
begin
select @execstring = 'CREATE FULLTEXT INDEX ON '
+ @newtabname +' KEY INDEX '
+ quotename( @keyname, '[') + ' ON '
+ quotename( @ftcat, '[') + ' WITH CHANGE_TRACKING OFF, NO POPULATION '
EXEC (@execstring)
end

if @action = 'drop'
begin
select @execstring = 'DROP FULLTEXT INDEX ON '
+ @newtabname

EXEC (@execstring)
end

if @action = 'activate'
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' ENABLE '

EXEC (@execstring)
end

if @action = 'deactivate'
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' DISABLE '

EXEC (@execstring)
end

if @action = 'start_change_tracking'
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' SET CHANGE_TRACKING MANUAL '

EXEC (@execstring)
end

if @action = 'stop_change_tracking'
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' SET CHANGE_TRACKING OFF '

EXEC (@execstring)
end

if @action = 'start_background_updateindex'
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' SET CHANGE_TRACKING AUTO '
EXEC (@execstring)
end

if @action = 'stop_background_updateindex'
begin
-- when table is not existing, we will call ALTER FULLTEXT to raise error for consistent error behavior.
if object_id(@newtabname, 'local') is null OR
ObjectProperty(object_id(@newtabname, 'local'), 'TableHasActiveFulltextIndex') = 0 OR
ObjectProperty(object_id(@newtabname, 'local'), 'TableFullTextBackgroundUpdateIndexOn') != 0
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' SET CHANGE_TRACKING MANUAL '
EXEC (@execstring)
end
end

if @action = 'update_index'
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' START UPDATE POPULATION '
EXEC (@execstring)
end

if @action = 'start_full'
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' START FULL POPULATION '
EXEC (@execstring)
end

if @action = 'start_incremental'
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' START INCREMENTAL POPULATION '
EXEC (@execstring)
end

if @action = 'stop'
begin
select @execstring = 'ALTER FULLTEXT INDEX ON '
+ @newtabname +' STOP POPULATION '
EXEC (@execstring)
end

-- SUCCESS --
return 0

sp_fulltext_semantic_unregister_language_statistics_db (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_fulltext_semantic_unregister_language_statistics_db()

MetaData:

 create proc sys.sp_fulltext_semantic_unregister_language_statistics_db  
as
begin
set nocount on
declare @returncode int
EXEC @returncode = sys.sp_fulltext_semantic_unregister_language_statistics_db_internal
return @returncode
end

sp_fulltext_semantic_register_language_statistics_db (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_fulltext_semantic_register_language_statistics_db(nvarchar @dbname)

MetaData:

 create proc sys.sp_fulltext_semantic_register_language_statistics_db  
@dbname sysname
as
begin
set nocount on
declare @returncode int
EXEC @returncode = sys.sp_fulltext_semantic_register_language_statistics_db_internal @dbname
return @returncode
end

sp_fulltext_recycle_crawl_log (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_fulltext_recycle_crawl_log(nvarchar @ftcat)

MetaData:

 create proc sys.sp_fulltext_recycle_crawl_log  
@ftcat sysname -- full-text catalog name
as
set nocount on

-- sp_fulltext_catalog will run under read committed isolation level --
set transaction isolation level READ COMMITTED

-- CHECK PERMISSIONS (must be a dbowner) --
if (is_member('db_owner') = 0)
begin
raiserror(15247,-1,-1)
return 1
end

if (db_name() in ('master','tempdb','model'))
begin
raiserror(9966, -1, -1)
return 1
end

-- VALIDATE PARAMS --
if @ftcat is null OR datalength(@ftcat) = 0 -- allow spaces in the name, but not a 0-length string
begin
raiserror(15600,-1,-1,'sys.sp_fulltext_recycle_crawl_log')
return 1
end

-- DISALLOW USER TRANSACTION --
set implicit_transactions off
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sys.sp_fulltext_recycle_crawl_log')
return 1
end

declare @ftcatid smallint
select @ftcatid = fulltext_catalog_id from sys.fulltext_catalogs where name = @ftcat
if @ftcatid is null
begin
declare @curdbname sysname
select @curdbname = db_name()
declare @curdbnamelen int
select @curdbnamelen = LEN(@curdbname)
raiserror(7641,-1,-1,@ftcat, @curdbnamelen, @curdbname)
return 1
end

DBCC CALLFULLTEXT ( 20, @ftcat)

return 0

Total Pageviews