Oracle

select a.table_name,b.COLUMN_NAME,b.DATA_TYPE from user_tables a
left join user_tab_columns b on a.table_name=b.table_name
where b.DATA_TYPE=‘TIMESTAMP(6)‘;

SQL Server
select b.name ColumnName,c.name SystemTypeName,a.name tablename,b.precision,b.scale from sys.tables a
left join sys.columns b on a.object_id=b.object_id
left join sys.types c on b.system_type_id=c.system_type_id
where c.name=‘date‘;