SELECT * FROM TABLE minus certain columns


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.
LVL 10
John ClaesSenior .Net Consultant & Technical AnalistAsked:
Who is Participating?
derobyConnect With a Mentor Commented:
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)

Aneesh RetnakaranDatabase AdministratorCommented:
> WITHOUT getting the ID column and whitout using the columnnames
i dont think it is possible .. , using column names its ok
SELECT [name]
FROM [database_name].[dbo].[syscolumns]
where [id] = (select [id] from [database_name].[dbo].[sysobjects] WHERE name = 'table_name')
and [name] != 'ID'
John ClaesSenior .Net Consultant & Technical AnalistAuthor Commented:

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.