sp_BlitzIndex -- code review to make sure we're joining on database ID

[BlitzErik]

Do you want to request a feature or report a bug?
Potential bug

What is the current behavior?
Since adding logic to check all databases, many joins now need to also join on database ID (don't get me started on schema ID -- most tables don't include that!)

When checking a single database, you get (okay, like 98%) correct results, but when checking all databases it's possible to get cross-database pollution when joins don't include database ID. For instance, some queries only join on object ID. This can overlap between databases. Boo and hiss.

What is the expected behavior?
Add safety joins on database ID to queries that carry it.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
All.

[BlitzErik]

Doing a little recon on this one. Here are all the necessary tables to look at. Thankfully not many are missing schema name. We need schema names for cases where there are multiple schema in a database where tables have the same names. There was an open issue a while back to have this run against a single schema, which isn't happening here, but we'll be a little bit closer if it becomes a requested feature.

The easy part of this is changing the temp tables and adding the info to the initial inserts. The annoying part is going to be going through all the checks and adding additional joins.

#IndexSanity: [database_id]
Has schema name

#IndexPartitionSanity: [database_id]
No schema name

#IndexSanitySize: [database_id]
No schema name

#IndexColumns: [database_id]
No schema name

#MissingIndexes: [database_name]
Has schema name

#ForeignKeys: [database_name]
No schema name

#Statistics: database_name
Has schema name

#ComputedColumns: database_name
Has schema name

[BrentOzar]

I salute you for doing this, sir.

[BlitzErik]

Cool, so the update for this join now having database id and schema name in it makes for a lot less work in later checks where we join on these IDs.