Skip to content

Instantly share code, notes, and snippets.

@DavidGoodwin
Forked from gplessis/auto_increment_capacity.sql
Last active August 29, 2015 14:02
Show Gist options
  • Save DavidGoodwin/1f1bcb7048d53fb877b7 to your computer and use it in GitHub Desktop.
Save DavidGoodwin/1f1bcb7048d53fb877b7 to your computer and use it in GitHub Desktop.
SELECT table_schema,
table_name,
data_type,
( CASE data_type
WHEN 'tinyint' THEN 255
WHEN 'smallint' THEN 65535
WHEN 'mediumint' THEN 16777215
WHEN 'int' THEN 4294967295
WHEN 'bigint' THEN 18446744073709551615
end >> IF(Locate('unsigned', column_type) > 0, 0, 1) ) AS MAX_VALUE,
AUTO_INCREMENT,
AUTO_INCREMENT*100/( CASE data_type
WHEN 'tinyint' THEN 255
WHEN 'smallint' THEN 65535
WHEN 'mediumint' THEN 16777215
WHEN 'int' THEN 4294967295
WHEN 'bigint' THEN 18446744073709551615
end >> IF(Locate('unsigned', column_type) > 0, 0, 1) ) AS percent_capacity
FROM
information_schema.columns
INNER JOIN
information_schema.tables USING (table_schema, table_name)
WHERE
table_schema NOT IN ( 'MYSQL', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA')
AND extra = 'auto_increment'
HAVING percent_capacity > 10
@DavidGoodwin
Copy link
Author

don't show fields which are no where near reaching any sort of useful(dangerous) capacity.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment