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