Selecting All Columns Except some columns RRS feed

  • Question

  • I have a table that contains 50 columns, I want to create a view that contains all the columns except 5 columns. I use the "*" to select the columns like this:

    SELECT  dbo.Names*
    FROM  dbo.Names

    Is there a way to exclude certain fields from the statement?

    Than you in advance,


    Hani Draidi GIS Engineer

    • Edited by Hani Draidi Tuesday, January 26, 2016 11:23 AM
    Tuesday, January 26, 2016 11:23 AM


All replies

  • Hi,

    no there is not. You should name all your columns if you create any permanent object like view. You prevent yourself from future errors if original table would change.

    Tuesday, January 26, 2016 11:36 AM
  • Hello Hani,

    The only way is to list all required columns explicit.

    Tip: In SSMS expand the table to see the node "Columns", then drag&drop "Columns" into your query window; SSMS will add all columns into your query and you only need to remove the unwanted column names. Or do a right mouse click on the table Name => "Select Top 1000" will do the same.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, January 26, 2016 11:36 AM
  • Script your table, and adjust SELECT to specify only needed column 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, January 26, 2016 12:20 PM
  • No option is there but can make a select script using Management studio and from there exclude columns as required .Another way Go to > New View >Select Table >After that select Columns >Select statement will be automatically generated as shown in below picture > Copy only the select statement > After that cancel to create view .

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Thursday, January 28, 2016 6:41 AM