SELECT * FROM TABLE    minus certain columns

Posted on 2006-05-19
Last Modified: 2012-08-13

I've got a dynamic evolving Table
I't can get other columnnames, other amount of column names all the time.
1 column is a certain asset of the table and that is the ID

Now I need a SQL Query that gives me ALL the Columns of that Table WITHOUT getting the ID column and whitout using the columnnames

Can somebody help me with this? this is urgent.
Question by:John Claes
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    > WITHOUT getting the ID column and whitout using the columnnames
    i dont think it is possible .. , using column names its ok
    LVL 11

    Expert Comment

    SELECT [name]
    FROM [database_name].[dbo].[syscolumns]
    where [id] = (select [id] from [database_name].[dbo].[sysobjects] WHERE name = 'table_name')
    and [name] != 'ID'
    LVL 11

    Accepted Solution

    You'll need two steps then I suppose, one that queries the meta-data, and one that does the actual select.

    Something like this I guess :

    DECLARE @sql varchar(8000)

    SELECT @sql = ''
    SELECT @sql = @sql + '[' + col.[name] + '], '
      FROM syscolumns col
      JOIN sysobjects obj
        ON obj.[id] = col.[id]
       AND obj.[name] = 'YourTableName'
     WHERE col.[name] NOT IN ('list', 'of', 'not', 'wanted', 'columns', 'here')
     ORDER BY colid

    IF @sql = ''
            SELECT 'Nothing to report'
            SELECT @sql = RTrim(@sql)
            SELECT @sql = Left(@sql, Len(@sql) - 1)

            SELECT @sql = 'SELECT ' + @sql + ' FROM YourTableName' -- add where/order by etc as needed

            -- test : PRINT @sql
            EXEC (@sql)

    LVL 10

    Author Comment

    by:John Claes

    i've made a stored procedure of it.
    I'ts a good one. i thought something like that but I couldn't combine the sys Query with the normal Query

    With Deroby's answer I now can do this Dynamicaly.

    Thx Alot.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now