SSMA

[Pat Hartman]

I used SSMA today for the first time in years to convert an old Access application and discovered a bug.

NONE of the hidden FK indexes were converted. Just to remind you all, when you create a FK relationship using the Relationship window, Access automagically creates an index on the FK field and names it "yourtablenameyourFKname". This index does NOT show up when you view the index dialog but you can see it if you run a documentation tool. If you make a mistake and create your own index on this field, you end up with two indexes on the same field and this I think might be one of those things that causes corruption as well as slowness.

I know I can create a bunch of CREATE .. INDEX statements but why is MS making me do this?

I installed SQL Server 2022 last month so of course the old version won't work so I downloaded version 9.5 for Access 32-bit and here I am, a very unhappy camper.

I would be very happy if someone were to tell me that SQL Server 2022 doesn't care, that it also creates a hidden index if the stupid DBA doesn't do it.


PS,

Does anyone have a query to share that will list all the indexes in a SQL Server database along with it’s type and a row for each column?

 

[Pat Hartman]

Wow! Who new this would be such a tough question? There's a lot more wrong with SSMA than what I posted above. I guess no one is using it any more. I'm working on code to generate the necessary Alter statements to add the missing indexes. Luckily I have a documentation tool that extracts the information I need to generate them. But it sure would have been nice if SSMA actually worked

 

[Minty]

I use it quite often when moving legacy Access systems into Azure or SQL Server for development.
Pretty sure the last time I did it from scratch, it created suitable indexes with the relevant constraints...

Let me go and have a look at one, and see.

 

[gregbowers]

It's frustrating to discover that SSMA didn't convert the hidden FK indexes in your Access application. It seems like an unnecessary manual step. Have you tried reaching out to Microsoft support for insights or a potential fix? Regarding SQL Server 2022, it's not uncommon for newer versions to have different behaviors. As for your PS, here's a query to list all indexes in a SQL Server database along with their type and columns:
SELECT t.name AS TableName, i.name AS IndexName, i.type_desc AS IndexType, c.name AS ColumnName FROM sys.indexes i INNER JOIN sys.tables t ON i.object_id = t.object_id INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id INNER JOIN sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id ORDER BY t.name, i.name, ic.index_column_id;


Hope this helps!

 

[Pat Hartman]

Welcome aboard Thanks for the query.

I am a former Access MVP so I actually have an in with MS. So far, no one has officially responded to my query but other MVPs have responded with very useful queries. Now that I have actually confirmed that SSMA is the culprit rather than me, I'll make my question more formal.

Here's one that lists the relationships.

select PrimaryKeyTable.name as  [Parent Table Name],
       PrimaryKeyColumn.name as [PK or UI Column Name],
       ForeignKeyTable.name as  [Child Table Name],
       ForeignKeyColumn.name as  [FK Column Name],
       case ForeignKeys.update_referential_action_desc + ' ' + ForeignKeys.delete_referential_action_desc
              when 'cascade no_action'   then 'Cascade Update only'
              when 'cascade cascade'     then 'Cascade Update / Cascade Delete'             
              when 'no_action cascade'   then 'Cascade Delete only'             
              when 'no_action no_action' then 'No Cascade Actions'
       end as [Relationship Type],
       ForeignKeys.name as ForeignKeyname,
       ForeignKeys.is_not_trusted,
       ForeignKeys.is_disabled,
       ForeignKeyColumn.is_nullable,
       ForeignKeyColumn.is_ansi_padded
 from sys.foreign_keys ForeignKeys
 join sys.foreign_key_columns ForeignKeyRelationships
   on (ForeignKeys.object_id = ForeignKeyRelationships.constraint_object_id)
 join sys.tables ForeignKeyTable
   on ForeignKeyRelationships.parent_object_id = ForeignKeyTable.object_id
 join sys.columns ForeignKeyColumn
   on (ForeignKeyTable.object_id = ForeignKeyColumn.object_id
  and ForeignKeyRelationships.parent_column_id = ForeignKeyColumn.column_id)
 join sys.tables PrimaryKeyTable
   on ForeignKeyRelationships.referenced_object_id = PrimaryKeyTable.object_id
 join sys.columns PrimaryKeyColumn
   on (PrimaryKeyTable.object_id = PrimaryKeyColumn.object_id
  and ForeignKeyRelationships.referenced_column_id = PrimaryKeyColumn.column_id)
order by PrimaryKeyTable.name, PrimaryKeyColumn.name;