SQL Server query to list all columns in table except the primary key and alternate keys

Can someone point me in the direction on how to write a query to pull all the column names开发者_C百科 from a table that are not primary keys or alternate keys. I would specifically like to exclude alternate keys from my queries.

DECLARE @tableName nvarchar(max) = 'table_name'

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = @tableName

EXCEPT

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS [tc]
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE [ku] ON tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
    AND ku.table_name = @tableName

I had to get the list of keys for a simmilar problem but I also had to use COALESCE. The problem was that COALESCE does not work with EXCEPT so I modified Kirill's solution to get rid of it:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS 
JOIN sysobjects ON TABLE_NAME = name
WHERE TABLE_NAME = @tablename
AND COLUMN_NAME NOT IN (
    SELECT name 
    FROM syscolumns 
    WHERE [id] IN (SELECT [id] 
        FROM sysobjects 
        WHERE [name] = @tablename)
        AND colid IN (SELECT SIK.colid 
        FROM sysindexkeys SIK 
        JOIN sysobjects SO ON SIK.[id] = SO.[id]  
        WHERE SIK.indid = 1
            AND SO.[name] = @tablename))

Maybe will be help for someone :)

Whether or not a column (or set of columns) is an alternate key cannot be decided by the database system (I use the alternate key term as a synonym for a column or set of columns that could have been primary key - they satisfy all PK requirements (unique and NOT NULL) but wasn't chosen to be primary key - for whatever reasons - see this article for reference).

There is a lot of "semantic" know-how behind knowing what constitutes a primary key (or an alternate key). While primary keys are "tagged" by being included in the primary key constraint, no such thing exists for alternate keys - since they didn't "make it" to become primary keys, they're just regular columns in your table that could be primary key - but aren't.

I don't see how SQL Server would be able to figure those out for you ...