SQL Server has the Information_Schema.Columns view, but it has several differences from MySQL:

• It uses "Catalog" for "Schema" (Schema means something else in SQL Server).
• No unified "Column_type". You have to construct it yourself from Data_Type and the Precisions/Scale columns
• It runs in the context of the currently connected database. That's a big one: it means you can't use it to look at all databases on the server. sys.Columns works the same way. SQL Server does not provide a way to show this for all databases. This was a deliberate choice (not an omission), and IMO the right choice, since there are security implications involved.

This will get you part of the way there, but again: only for one database at a time:

SELECT table_catalog 'Database', table_schema + '.' + table_name 'Table', column_name 'Column'
    , data_type + coalesce( '(' + cast(character_maximum_length as varchar(5))+ ')',
                            '(' + cast(numeric_precision as varchar(3)) + coalesce(',' + cast(nullif(numeric_scale,0) as varchar(3)),'') + ')',
                            '(' + cast(nullif(datetime_precision,case data_type when 'date' then 0 when 'datetime' then 3 end ) as varchar(2)) + ')',
                            '') 'Type'
FROM information_schema.columns c
INNER JOIN (VALUES 
   ('%cnpj%'), ('%cpf%'), ('%nom%'), ('%fon%'), ('%tel%'), ('%mail%'), ('%end%'),
   ('%cel%'),('%gen%'), ('%sex%'), ('%elet%'), ('%doc%'), ('%mae%'), ('%nasc%')
) filter(col) ON c.column_name like filter.col

The only option I've found to get around this is making a temp table, putting the query in a varchar variable as an INSERT/SELECT for the temp table with a USE [?] at the top, run it repeatedly using sp_msforeachdb , then select from the temp table:

CREATE Table #ServerColumns (
    [Database] nvarchar(128), 
    [Table] nvarchar(257), 
    [Column] nvarchar(128), 
    [Type] nvarchar(137)
);

DECLARE @query nvarchar(1000) = 
  'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN
    USE [?]
    INSERT INTO #ServerColumns 
    SELECT table_catalog ''Database'', table_schema + ''.'' + table_name ''Table'', column_name ''Column''
        , data_type + coalesce( ''('' + cast(character_maximum_length as varchar(5))+ '')'',
                                ''('' + cast(numeric_precision as varchar(3)) + coalesce('','' + cast(nullif(numeric_scale,0) as varchar(3)),'''') + '')'',
                                ''('' + cast(nullif(datetime_precision,case data_type when ''date'' then 0 when ''datetime'' then 3 end ) as varchar(2)) + '')'',
                                '''') ''Type''
    FROM information_schema.columns c
    INNER JOIN (VALUES 
       (''%cnpj%''), (''%cpf%''), (''%nom%''), (''%fon%''), (''%tel%''), (''%mail%''), (''%end%''),
       (''%cel%''),(''%gen%''), (''%sex%''), (''%elet%''), (''%doc%''), (''%mae%''), (''%nasc%'')
    ) filter(col) ON c.column_name like filter.col;
   END'

EXEC sp_msforeachdb @query
SELECT * FROM #ServerColumns
DROP TABLE #ServerColumns

This takes advantage of a common pattern on SQL Server for skipping the system databases ( master , model , msdb , and tempdb ), which seemed appropriate to the question.

FWIW, this table-value constructor filter technique also works on MySQL. You have to prepend the ROW keyword for each entry, but it's still a lot shorter and more scalable than repeating the LIKE expression over and over.

There are couple of differencies for the query in SQL Server. In SQL Server the information_schema.columns contains info from the current database, not all databases. Secondly, the column_type as such does not exists. You need to combine data_type and character_maximum_length .

select table_catalog as 'Database', table_name as 'Table', column_name as 'Column',
 data_type+iif(character_maximum_length is not null, '('+iif(character_maximum_length=-1, 'max', cast(character_maximum_length as varchar(10)))+')', '') as 'Type' 
from information_schema.columns 
where (
     column_name like '%cnpj%' 
  or column_name like '%cpf%' 
  or column_name like '%nom%' 
  or column_name like '%fon%' 
  or column_name like '%tel%' 
  or column_name like '%mail%' 
  or column_name like '%end%' 
  or column_name like '%cel%' 
  or column_name like '%gen%' 
  or column_name like '%sex%' 
  or column_name like '%elet%' 
  or column_name like '%doc%' 
  or column_name like '%mae%' 
  or column_name like '%nasc%'
)