Make Select in all columns except in 2 specific [duplicate]

Personally, I would redesign the table so that you do not have so many columns, maybe dividing the table into 2, with a structure similar to this one (obviously, I do not know the types of your fields, it's just an approximation):

create table master (
  id int not null primary key,
  mapa int,
  numero int  
);

create table detail (
  id int not null primary key,
  fk_id int not null,
  mob int not null,
  seq int not null,
  constraint detail_fk foreign key (fk_id) references master(id)
);

In this way, instead of having a row with mob1, mob2, mob3, ..., mob400 , you would have 400 records in the second table sharing the same foreign key, but with different values for seq .

This structure would simplify your problem of having to handle hundreds of columns in your query.

But if you can not (or do not want to) make the change, and you really want to do select of all the columns minus those 2, you could use the technique proposed in this solution: link .

It would be a bit complicated in a query, but it works well:

declare @cols varchar(max), @query varchar(max);
SELECT  @cols = STUFF
    (
        ( 
            SELECT DISTINCT '], [' + name
            FROM sys.columns
            where object_id = (
                select top 1 object_id from sys.objects
                where name = 'tbl' -- reemplaza "tbl" por el nombre de tu tabla
            )
            and name not in ('mapa', 'numero') -- excluir las 2 columnas aquí
            FOR XML PATH('')
        ), 1, 2, ''
    ) + ']';

SELECT @query = 'select ' + @cols + ' from tbl'; -- reemplaza "tbl" por el nombre de tu tabla
EXEC (@query);

Demo

And one more option, is to use the same previous technique, but instead of excluding the unwanted columns, you could only include the columns that begin with mob . This may be preferable in your case:

declare @cols varchar(max), @query varchar(max);
SELECT  @cols = STUFF
    (
        ( 
            SELECT DISTINCT '], [' + name
            FROM sys.columns
            where object_id = (
                select top 1 object_id from sys.objects
                where name = 'tbl' -- reemplaza "tbl" por el nombre de tu tabla
            )
            and name like 'mob%' -- incluir las columnas que comienzan por "mob"
            FOR XML PATH('')
        ), 1, 2, ''
    ) + ']';

SELECT @query = 'select ' + @cols + ' from tbl'; -- reemplaza "tbl" por el nombre de tu tabla
EXEC (@query);

Demo

As @Andre commented, it is not possible to do it the way

SELECT * NOT IN(Mapa, numero)
FROM Tabla

Now to solve it, you could use the following:

SELECT *
FROM Vista

SELECT * INTO #TablaTemporal
FROM Tabla
WHERE mapa = 1

/* A continuación borrás las columnas que no necesitás */
ALTER TABLE #TablaTemporal
DROP COLUMN mapa
ALTER TABLE #TablaTemporal
DROP COLUMN numero

/* Por ultimo, consultas la tabla y la borras*/
SELECT * FROM #TablaTemporal

DROP TABLE #TablaTemporal

This is not possible, you can only bring all the data '*' or specify the columns you need.

If the columns have a consecutive, you can assemble the query and place the columns you need excluding "Map and Number".