locked
columns in select query RRS feed

  • Question

  • hi,

    let us suppose that there are 15 columns in a table.

    i would like to select 14 of them. so should i need to write all the column names (or numbers) in the select query?

    Is there any other way to select all 14 columns with out mentioning them in the select query? If yes , please specify one example.

    Thanks.



    Friday, August 5, 2011 6:33 AM

Answers

  • In SSMS, you can drag&drop the *columns* folder to the query window. It will paste all columns and you can just remove the one(s) you don't want.
    Tibor Karaszi, SQL Server MVP | web | blog
    • Proposed as answer by Chirag Shah Saturday, August 6, 2011 11:01 AM
    • Marked as answer by Peja Tao Monday, August 8, 2011 5:52 AM
    Saturday, August 6, 2011 8:13 AM
  • Hi Sudeep Puvvadi, 

    i would like to select 14 of them. so should i need to write all the column names (or numbers) in the select query?

    There is no inbuilt functionality as per your requirement. 

    Is there any other way to select all 14 columns with out mentioning them in the select query? If yes , please specify one example?

    Here I have two options as per your requirement 

    Option1: Using SSMS 2005 and above

    Right click on table --> Script Table as --> Select To --> New Query Editor Window/File/Clipboard see the below pic

    Here all the columns will come into SELECT query you can delete the columns whichever you do not want


    Option2: Use the below script to filter the column names 

    --Example
    CREATE TABLE EMPLOYEE(EMPID INT,EMPNAME NVARCHAR(10),SALARY INT)
    GO
    ---- 
    set nocount on
    DECLARE @c varchar(4000), @t varchar(128)
    SET @c = 'SELECT '
    SET @t='EMPLOYEE'
    
    SELECT @c = @c + c.name + ', '
     FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id
     WHERE o.name = @t AND C.NAME NOT IN('SALARY') -- Filter column names Here I do not want salary column
     ORDER BY colid
    SELECT Substring(@c, 1, Datalength(@c) - 2) 
    
    SELECT @C = @C +'FROM ' + @t
    PRINT @C
    --- This query will give you the column name data type and length
    select column_name, data_type, character_maximum_length from information_schema.columns
    where table_name = 'EMPLOYEE'
    

    This will solve your problem





    Rajesh Kasturi Please click the Mark as Answer button if my post solves your problem.
    • Proposed as answer by Kasturi Rajesh Friday, August 5, 2011 8:31 AM
    • Marked as answer by Peja Tao Monday, August 8, 2011 5:52 AM
    Friday, August 5, 2011 8:28 AM

All replies

  • Simple solution to your problem is Yes, you have to mention all the 14 column names in the select list to display only 14 of them out of 15.

    Or the other way around is to write a Dynamic SQL,

    I suggest you to not to use it for this purpose.

     

    Surendra


    Nothing is Permanent... even Knowledge....
    My Blog
    Friday, August 5, 2011 7:53 AM
  • Hi Sudeep Puvvadi, 

    i would like to select 14 of them. so should i need to write all the column names (or numbers) in the select query?

    There is no inbuilt functionality as per your requirement. 

    Is there any other way to select all 14 columns with out mentioning them in the select query? If yes , please specify one example?

    Here I have two options as per your requirement 

    Option1: Using SSMS 2005 and above

    Right click on table --> Script Table as --> Select To --> New Query Editor Window/File/Clipboard see the below pic

    Here all the columns will come into SELECT query you can delete the columns whichever you do not want


    Option2: Use the below script to filter the column names 

    --Example
    CREATE TABLE EMPLOYEE(EMPID INT,EMPNAME NVARCHAR(10),SALARY INT)
    GO
    ---- 
    set nocount on
    DECLARE @c varchar(4000), @t varchar(128)
    SET @c = 'SELECT '
    SET @t='EMPLOYEE'
    
    SELECT @c = @c + c.name + ', '
     FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id
     WHERE o.name = @t AND C.NAME NOT IN('SALARY') -- Filter column names Here I do not want salary column
     ORDER BY colid
    SELECT Substring(@c, 1, Datalength(@c) - 2) 
    
    SELECT @C = @C +'FROM ' + @t
    PRINT @C
    --- This query will give you the column name data type and length
    select column_name, data_type, character_maximum_length from information_schema.columns
    where table_name = 'EMPLOYEE'
    

    This will solve your problem





    Rajesh Kasturi Please click the Mark as Answer button if my post solves your problem.
    • Proposed as answer by Kasturi Rajesh Friday, August 5, 2011 8:31 AM
    • Marked as answer by Peja Tao Monday, August 8, 2011 5:52 AM
    Friday, August 5, 2011 8:28 AM
  • Yes, you have write the 14 column names in the select query.
    Ali Hamdar (alihamdar.com - www.ids.com.lb)
    Friday, August 5, 2011 8:43 AM
  • In SSMS, you can drag&drop the *columns* folder to the query window. It will paste all columns and you can just remove the one(s) you don't want.
    Tibor Karaszi, SQL Server MVP | web | blog
    • Proposed as answer by Chirag Shah Saturday, August 6, 2011 11:01 AM
    • Marked as answer by Peja Tao Monday, August 8, 2011 5:52 AM
    Saturday, August 6, 2011 8:13 AM
  • Hi TiborK, 

    Nice to know the cool option, this is useful thanks 


    Rajesh Kasturi Please click the Mark as Answer button if my post solves your problem.
    Tuesday, August 9, 2011 6:30 AM