4

I want to create a full text index but I am not sure if the index name is the same in all databases. So, I plan to write the below select and get the name of the index.

DECLARE @keyIndex NVARCHAR(100)
SELECT @keyIndex = name FROM sysobjects WHERE xtype = 'PK' AND parent_obj = OBJECT_ID(N'[dbo].[Table1]')

Below is the Create statement for full text index which I have tried:

CREATE FULLTEXT INDEX ON dbo.Table1
    (
        [Name] Language 1033,
        [Description] Language 1033
    ) 
    KEY INDEX [@keyIndex] 
    WITH STOPLIST = SYSTEM, CHANGE_TRACKING AUTO;

It gives this error:

Msg 7653, Level 16, State 1, Line 11
'@keyIndex' is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key.

I need some help on how to dynamically insert the value for key index.

stech
  • 675
  • 2
  • 9
  • 22

1 Answers1

1

Just build the string manually, and then execute it: 

DECLARE @SQLString nvarchar(1000)
DECLARE @ParmDefinition nvarchar(1000)
DECLARE @keyIndex nvarchar(100);
set @keyIndex = 'Id'
SET @SQLString =
     N'CREATE FULLTEXT INDEX ON dbo.objects
    (
        [Name] Language 1033,
        [Description] Language 1033
    ) 
    KEY INDEX ' + @keyIndex +N' 
    WITH STOPLIST = SYSTEM, CHANGE_TRACKING AUTO;';
EXEC(@SQLString)
macwier
  • 1,063
  • 1
  • 6
  • 19
  • Doesn't work, gives error - Incorrect syntax near '@keyIndex'. Expecting ID or Quoted_ID – stech Jul 03 '13 at 09:53
  • Ok, im sorry that i didn't try it myself, i was sure it will work. I tried this solution.. – macwier Jul 03 '13 at 10:24
  • I there another way? Building the string manually is not pretty man. You lose alot of features doing that way. For example intellisense – Mustafa Apr 29 '18 at 22:58