Is there a SQL command I could run to check if our foreign key columns are of the correct type?

Question

We recently discovered that one of our foreign keys was of type varchar instead of bigInt. It was a huge bottleneck and was consuming something like 75% of our RDS and had been in the codebase for 3 years...

I wondered if there was any kind of command I could run against our schema to check if other columns have the wrong type assigned? Something like:

// pseudo AF but:
select columns.* from our_schema where column.name LIKE '_id' and column.type = 'varChar'

Answer 1

I was able to achieve this within my Laravel environment like so:

    $schema = collect(DB::connection()->getDoctrineSchemaManager()->listTableNames())->map(function ($item, $key) {
        return [
          'name' => $item,
          'columns' => DB::getSchemaBuilder()->getColumnListing($item)
        ];
      });

    foreach ($schema as $table) {
        foreach ($table['columns'] as $columnName) {
            if (!strpos($columnName, '_id')) {
                continue;
            }
            try {

                $columnType = DB::getSchemaBuilder()->getColumnType($table['name'], $columnName);

                if ($columnType !== 'bigint') {
                    logger(["column name" => $columnName, "table name" => $table['name'], "column type" => $columnType]);
                }
            } catch (\Exception $e) {
                // do nothing
            }
        }
    }