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

No comments:

Post a Comment

Total Pageviews