SSMS (1 Viewer)

[Pat Hartman]

I'm having some trouble with some of the tools in SSMS. For example, right clicking on a table and selecting the Script option, I can create a script to create a table. However, it is NOT scripting non-unique indexes. So, none of my FK indexes and none of my other indexes if they are non-unique are in the DDL. I have some insight into the cause but not the solution. I decided to right click on the database and use the tasks/generate script option. This brings up a wizard so I chose a table with the problem. As I walked through the wizard I saw the summary item and drilled into it. It has an item for Options and in the Table/View item is an option for --- Script Indexes but it is set to false. There is another item for --- Script Unique Keys and it is set to True. So, I think this solves the mystery of what happened to the indexes but I can't find the setting to fix the problem. The wizard doesn't let me change the settings and I can't find any other place that they might be. Please use pictures if possible so I can see where you are clicking. I am using SSMS 2019 but I also have 2017 available. I installed 2019 because 2017 no longer lets me dock windows and so working with it is pretty annoying maybe you have the solution to that also. I haven't tried to reinstall but I guess I could go searching.

I don't think it matters but I'm using the Dev version of SQL Server 2022.

 

[AHeyne]

In the menu of SSMS (I use 19.3) click "Tools", then "Options", expand "SQL Server Object Explorer" and then select "Scripting".
There are the options you're looking for which set the defaults for the mentioned wizard.

I think there is no necessarity for screenshots.

 

[Pat Hartman]

Thanks. I swear I clicked on all the options. I guess I need to clean my glasses. My last problem is figuring out how to script the FK indexes. If I pick one table and say to script related, I get the FK indexes but I also get the Create's for the related tables and their indexes, etc. That's just confusing and annoying with more than 20 relationships. I don't want to do that, I just the FK's but I'll take the table because I seem to not have an option.. None of the settings seems to give me what I want. I am now seeing the PK and the non-FK indexes both unique and not unique but the FK indexes are still MIA. I've been trying various options a couple at a time. I thought if I just scripted the whole database, I would get all the FK indexes but they don't show up.

My problem is that a client DBA managed to drop all the relationships so what I really want is a way to just script ALL relationships but that doesn't seem to be an option. Also, I can't figure out how to get a list of all indexes in the database. The FK indexes are just not showing up anywhere so I can't tell if she dropped those also. What a PITA. This mess up happened probably last May when they started their conversion to move the application to the new company's server. I only discovered it 2 weeks ago. The application only allows deletes in a very limited way so I'm hoping that I don't have orphans to fix. They also didn't bother to copy any backups so I don't have the option of looking at a version of the database from the time before when I think the drop happened.

 

[AHeyne]

So you want to script all ForeignKeys of all tables in your original database (because in the customers database they are missing)?
This seems to be right easy, with the only disadvantage that the script also contains the creation of the tables.
However, since the ForeignKeys are at the end of the script and the tables are at the beginning, you can simply cut them out after the script has been created.


Under "Tasks", "Generate Scripts" select all the tables and set this in the advanced options:

Regarding "Also, I can't figure out how to get a list of all indexes in the database." it seems to be similar, just using this options:

 

[Minty]

Hi Pat, there are a number of scripts here

Script out all SQL Server Indexes in a Database using T-SQL

Sometimes as a DBA we need to generate a T-SQL script for dropping and creating indexes in our SQL Server databases. However, SQL Server Management Studio does not have an easy way via the wizard to complete these tasks all at the same time. In this tip,

www.mssqltips.com

That will script all the indexes from a database and various other things.

 

[cheekybuddha]

Not 100% tested, but you can query the database to make the scripts yourself:

SELECT
  rc.CONSTRAINT_NAME ConstraintName,
  IIF(tm.TABLE_NAME LIKE '% %', QUOTENAME(tm.TABLE_NAME), tm.TABLE_NAME) TblName,
  cu.COLUMN_NAME ForeignKeyCol,
  IIF(tc.TABLE_NAME LIKE '% %', QUOTENAME(tc.TABLE_NAME), tc.TABLE_NAME) ReferencedTbl,
  ccu.COLUMN_NAME ReferencedCol,
  rc.UPDATE_RULE OnUpdate,
  rc.DELETE_RULE OnDelete,
  'ALTER TABLE ' +
    IIF(tm.CONSTRAINT_SCHEMA LIKE '% %', QUOTENAME(tm.CONSTRAINT_SCHEMA), tm.CONSTRAINT_SCHEMA) + '.' +
    IIF(tm.TABLE_NAME LIKE '% %', QUOTENAME(tm.TABLE_NAME), tm.TABLE_NAME) +  ' ' +
  'ADD CONSTRAINT ' + IIF(rc.CONSTRAINT_NAME LIKE '% %', QUOTENAME(rc.CONSTRAINT_NAME), rc.CONSTRAINT_NAME) + ' ' +
  'FOREIGN KEY (' + IIF(cu.COLUMN_NAME LIKE '% %', QUOTENAME(cu.COLUMN_NAME), cu.COLUMN_NAME) + ') ' +
  'REFRENCES ' + IIF(tc.TABLE_NAME LIKE '% %', QUOTENAME(tc.TABLE_NAME), tc.TABLE_NAME) + ' ' +
  '(' + IIF(ccu.COLUMN_NAME LIKE '% %', QUOTENAME(ccu.COLUMN_NAME), ccu.COLUMN_NAME) + ') ' +
  'ON UPDATE ' + rc.UPDATE_RULE + ' ' +
  'ON DELETE ' + rc.DELETE_RULE AS DDL 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tm
        ON rc.CONSTRAINT_NAME = tm.CONSTRAINT_NAME
       AND rc.CONSTRAINT_CATALOG = tm.CONSTRAINT_CATALOG
       AND rc.CONSTRAINT_SCHEMA = tm.CONSTRAINT_SCHEMA
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        ON rc.UNIQUE_CONSTRAINT_NAME = tc.CONSTRAINT_NAME
       AND rc.UNIQUE_CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
       AND rc.UNIQUE_CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
        ON rc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
       AND rc.CONSTRAINT_CATALOG = cu.CONSTRAINT_CATALOG
       AND rc.CONSTRAINT_SCHEMA = cu.CONSTRAINT_SCHEMA
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
        ON tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
       AND tc.CONSTRAINT_CATALOG = ccu.CONSTRAINT_CATALOG
       AND tc.CONSTRAINT_SCHEMA = ccu.CONSTRAINT_SCHEMA
WHERE tm.CONSTRAINT_TYPE = 'FOREIGN KEY'
  AND  tc.TABLE_NAME LIKE '%'
ORDER BY
  tm.TABLE_NAME
;

 

[Pat Hartman]

@AHeyne You'd think the settings would work.
I'm talking about scripting the INDEXES on the FKs. The actual FKs are fine.
Just using the Script FKs doesn't work either for a single table but also for scripting the whole database. The script for the whole database is
1. Create tables with PK
2. Create unique indexes
3. Create non-unique indexes --------------------- this should include the indexes for the FKs.
4. Create default constraints
5. Create FKs
6. Create set ZLS strings to No

I just used SSMA to convert an ACE db to SQL Server and I'm trying to verify it. It seems like SSMA did NOT create the indexes on the FK fields which is a pretty glaring error.

I'm going to try some of the suggestions to script all indexes to see if those find the missing indexes.

 

[Pat Hartman]

@cheekybuddha Thanks, that's a good query to keep. Others have sent it to me. And you're right. I can use this to generate the Create Index statements but I am trying to confirm that there is a problem with SSMA because it should have created these indexes. They exist in the Access database. The only difference is that these particular indexes are actually created by Access rather than me. That shouldn't make a difference though.

 

[Pat Hartman]

@Minty Thanks, that seems to confirm that those pesky indexes are MIA. It is looking more and more like SSMA is the culprit. You'd think someone would have noticed this glaring error.

 

[cheekybuddha]

I am trying to confirm that there is a problem with SSMA because it should have created these indexes.

You wouldn't be confirming anything new - AIUI, SSMA is notoriously buggy.

Although more work, I would take control of it myself and avoid not knowing whether the issue is the tool or you!

Somewhere I have some unfinished code to create DDL from an Access db - I'll try and dig it out this week.

 

[Pat Hartman]

Thanks to all of you. I'm going to mark this thread as solved.

It appears that it is SSMA that is the culprit. Some thing is different about the indexes created by Access for the FKs so if you are upsizing a database, this is something you need to check. The code posted in the various answers will confirm that the indexes are MIA so they are very useful.

I discovered years ago that Access was creating the FK indexes automatically so I stopped doing it. I'll do some experimenting to see if I create the proper index ahead of time whether or not Access still creates the index and try to remember to post back the answer. I think Access still does because I have discovered many cases of two indexes on the FK field. One created by me and one created by Access.

I just confirmed that even if you create the index on a FK field ahead of defining the relationship, Access STILL creates the extra index and you end up with two indexes on the same column.

So, either Access needs to get smarter and not create the unnecessary index or SSMA needs to transfer these Access created indexes because unless you have created a tool like I did that uses the index collections to list all the indexes, you would NEVER know that Access was helping you behind your back since these automagically created indexes are not visible in the indexes dialog.