Why is SSMS-produced script missing indexes?

Question

SSMS 17.4, SQL Server 2017 Developer's Edition on Win10 1709

I have installed the WorldWideImporters sample database. One of the tables, Sales.Customers, has several foreign keys AND several foreign key indexes. When scripting the table (Script Table, CREATE To…), the script includes the foreign keys, but not the foreign key indexes. If I just change the name of the table and run the generated script, the table is created with all of the FK constraints, but none of the FK indexes.

For something you can see even if you don't have the WWI sample installed, I did this.

CREATE TABLE bar (
  bar_id int,
  col1 varchar(20),
  CONSTRAINT pk_bar PRIMARY KEY CLUSTERED (bar_id)
)
CREATE TABLE foo (
  foo_id int,
  foobar_id int,
  col1 varchar(20),
  CONSTRAINT pk_foo PRIMARY KEY CLUSTERED (foo_id)
)
ALTER TABLE foo WITH CHECK
  ADD CONSTRAINT FK_bar FOREIGN KEY (foobar_id) REFERENCES bar (bar_id)
ALTER TABLE foo CHECK CONSTRAINT FK_bar

That creates two tables, with foo having a FK constraint to bar. I then scripted the table from SSMS.

CREATE TABLE [dbo].[foo](
    [foo_id] [int] NOT NULL,
    [foobar_id] [int] NULL,
    [col1] [varchar](20) NULL,
 CONSTRAINT [pk_foo] PRIMARY KEY CLUSTERED 
(
    [foo_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [USERDATA]
) ON [USERDATA]
GO

ALTER TABLE [dbo].[foo]  WITH CHECK ADD  CONSTRAINT [FK_bar] FOREIGN     KEY([foobar_id])
REFERENCES [dbo].[bar] ([bar_id])
GO

ALTER TABLE [dbo].[foo] CHECK CONSTRAINT [FK_bar]
GO

So far, so good.

But then I added an index on foo on the FK column.

CREATE NONCLUSTERED INDEX FK_bar ON foo (foobar_id)

Scripting the table then produces the exact same script as above. Thus, SSMS is producing the same script whether there's an index on the FK column or not. (I confirmed with sp_helpindex that the FK index does indeed exist.)

Is this a bug in SSMS or am I mis-understanding something?

Pome

There is a list of scripting options in SSMS settings, and indices might be turned off by default (can't check this right now). – Abukir

Answer 1

Scripting out the index is turned off by default in SSMS. Personally, that is one of the first things I turn back on along with scripting permissions and triggers. You can find this setting by:

1. In SSMS, open the Tools menu and pick Options
2. Scroll down to SQL Server Object Explorer and expand the tree
3. Click on the Scripting node and change Script indexes to true

Sevik

Wow. I've used SSMS since SQL2K, I've never seen that (I don't use Object Explorer, so never bothered with it), and I don't remember scripts not having indexes before. Did that default change in recent versions of SSMS? Regardless, thanks very much. – Pome

@Pome It's been that way as long as I can recall. – Sevik