May 16, 2012

sp_MShelpfulltextindex (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_MShelpfulltextindex(nvarchar @tablename)

MetaData:

   
create proc sys.sp_MShelpfulltextindex
@tablename nvarchar(517)
as

create table #sphelpft
(
ind_name nvarchar(128) COLLATE database_default NOT NULL,
col1 nvarchar(128) COLLATE database_default,
col2 nvarchar(128) COLLATE database_default,
col3 nvarchar(128) COLLATE database_default,
col4 nvarchar(128) COLLATE database_default,
col5 nvarchar(128) COLLATE database_default,
col6 nvarchar(128) COLLATE database_default,
col7 nvarchar(128) COLLATE database_default,
col8 nvarchar(128) COLLATE database_default,
col9 nvarchar(128) COLLATE database_default,
col10 nvarchar(128) COLLATE database_default,
col11 nvarchar(128) COLLATE database_default,
col12 nvarchar(128) COLLATE database_default,
col13 nvarchar(128) COLLATE database_default,
col14 nvarchar(128) COLLATE database_default,
col15 nvarchar(128) COLLATE database_default,
col16 nvarchar(128) COLLATE database_default
)

set nocount on

-- all the possible full text unique indexes --
declare @objid int
select @objid = object_id(@tablename, N'local')
insert #sphelpft
select i.name,
columnproperty( @objid, index_col(@tablename, i.indid, 1), N'AllowsNull'),
columnproperty( @objid, index_col(@tablename, i.indid, 2), N'AllowsNull'),
columnproperty( @objid, index_col(@tablename, i.indid, 3), N'AllowsNull'),
columnproperty( @objid, index_col(@tablename, i.indid, 4), N'AllowsNull'),
columnproperty( @objid, index_col(@tablename, i.indid, 5), N'AllowsNull'),
columnproperty( @objid, index_col(@tablename, i.indid, 6), N'AllowsNull'),
columnproperty( @objid, index_col(@tablename, i.indid, 7), N'AllowsNull'),
columnproperty( @objid, index_col(@tablename, i.indid, 8), N'AllowsNull'),
columnproperty( @objid, index_col(@tablename, i.indid, 9), N'AllowsNull'),
columnproperty( @objid, index_col(@tablename, i.indid, 10), N'AllowsNull'),
columnproperty( @objid, index_col(@tablename, i.indid, 11), N'AllowsNull'),
columnproperty( @objid, index_col(@tablename, i.indid, 12), N'AllowsNull'),
columnproperty( @objid, index_col(@tablename, i.indid, 13), N'AllowsNull'),
columnproperty( @objid, index_col(@tablename, i.indid, 14), N'AllowsNull'),
columnproperty( @objid, index_col(@tablename, i.indid, 15), N'AllowsNull'),
columnproperty( @objid, index_col(@tablename, i.indid, 16), N'AllowsNull')
from dbo.sysindexes i where
@objid = i.id and
IndexProperty(@objid, i.name, N'IsUnique') = 1 and
IndexProperty(@objid, i.name, N'UserKeyCount') = 1 and
-- 450 byte MAX --
exists (select * from dbo.syscolumns where id = @objid and name = Index_col(@tablename, IndexProperty(@objid, i.name, N'IndexId'), 1)
and length <= 450)

-- Now we need to filter out the indexes which the associated key(s) are nullable --
-- Each index can have up to 16 associated keys, all of them need to be non-nullalbe for the index to be qualified as a full text index --
delete #sphelpft where col1 = 1 or col2 = 1 or col3 = 1 or col4 = 1 or col5 = 1 or col6 = 1 or col7 = 1 or col8 = 1 or
col9 = 1 or col10 = 1 or col11 = 1 or col12 = 1 or col13 = 1 or col14 = 1 or col15 = 1 or col16 = 1

select ind_name from #sphelpft
DROP TABLE #sphelpft

No comments:

Post a Comment

Total Pageviews