-- -----------------------------------------------------------------------
-- Filename:   my_gdpr_cols.sql
-- Purpose:    Extracts candidate columns for personal, sensitive, critical, ... data
-- Date:       14-Feb-2017
-- Author:     meo
--
-- Updated By  meo
-- Description Rudimental...
-- -----------------------------------------------------------------------


SELECT table_schema, table_name, column_name,
       DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLUMN_COMMENT
  FROM information_schema.columns c
 WHERE c.table_schema not in('information_schema', 'mysql', 'performance_schema', 'sys')
   and (column_name like '%FIRST%NAME%' OR
	column_name like '%LAST%NAME%' OR
	column_name like '%SURNAME%' OR
	column_name like '%COGNOME%' OR
	column_name like '%FULL%NAME%' OR
	column_name like '%USER%NAME%' OR
	column_name like '%USER%LOGIN%' OR
	column_name like '%MAIL%' OR
	column_name like '%POSTA%' OR
	column_name like '%TELEP%' OR
	column_name like '%EXTENS%' OR
	column_name like '%TELEF%' OR
	column_name like '%SSN%' OR
	column_name like '%FISC%' OR
	column_name like '%IBAN%' OR
	column_name like '%CCARD%' OR
	column_name like '%BANK%' OR
	column_name like '%BANCA%' OR
	column_name like '%PAYP%' OR
	column_name like '%PASS%' OR
	column_name like '%RELIGIO%' OR
	column_name like '%ACUZIE%' OR
	column_name like '%PATHOL%')
 order by 1,2,3;