CODEWITHSUNDEEP
mysqlchecksum
Raptor
Raptor

Reputation: 54278

Hash MySQL database schema

I would like to make a hash / signature of MySQL database schema (without data), in order to have a checksum of it, to ensure it is not modified by others.

How can I achieve it ?

Upvotes: 9

Views: 4407

Answers (2)

Евгений Кочетков
Евгений Кочетков

Reputation: 31

SELECT HEX( CRC32 ( SUM( CRC32( CONCAT(
   COALESCE(TABLE_NAME,''),
   COALESCE(COLUMN_NAME,''),
   COALESCE(ORDINAL_POSITION,''),
   COALESCE(COLUMN_DEFAULT,''),
   COALESCE(IS_NULLABLE,''),
   COALESCE(DATA_TYPE,''),
   COALESCE(CHARACTER_MAXIMUM_LENGTH,''),
   COALESCE(CHARACTER_OCTET_LENGTH,''),
   COALESCE(NUMERIC_PRECISION,''),
   COALESCE(NUMERIC_SCALE,''),
   COALESCE(CHARACTER_SET_NAME,''),
   COALESCE(COLLATION_NAME,''),
   COALESCE(COLLATION_NAME,''),
   COALESCE(COLUMN_TYPE,''),
   COALESCE(COLUMN_KEY,'')
  ) ) ) ) ) AS 'hash'
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'database_name';

Upvotes: 3

Nemoden
Nemoden

Reputation: 9056

You need table checksum as far as I understand your question:

checksum table `table`

so, just make a checksum of an empty table, I guess

Upvotes: 10

Related Questions