Get only table columns with DbConnection.GetSchema method

Question

With C# and SQL Server 2005 and by using DbConnection.GetSchema() method, I want to get all a table's columns (not of views) only. I have found two collection names related to this

1. Columns that returns table and views' columns
2. ViewColumns returns all the view's columns

Neither of above two returns table columns only, nor they have any property to filter Table-columns.

Any help is respected.

Answer 1

I don't see any easy way to do this with this particular API you're trying to achieve this with - but why not just use a query like this to get your information?

SELECT
    c.name AS 'ColumName',
    ty.Name AS 'TypeName',
    c.max_length,
    c.is_identity,
    c.is_nullable,
    t.name AS 'TableName'
FROM sys.columns c
INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
INNER JOIN sys.tables t ON c.object_id = t.object_id

Just load that into a SqlCommand and execute it against the open connection you have and read the result into some DataTable or other structure for your use. This gives you only table columns - and all of them.