getting the columns in sql server table

I want to build a page that will handle all my list table... how can i get the names of the colums in specific table if i got the table name?

Use the ColumnName property in the DataColumn object. Here's an example:

// Populate the the row with the column names

foreach (DataColumn dcin dt.Columns)

{

_ColIndex++;

oSheet.Cells[_RowIndex, _ColIndex] = dc.ColumnName;

// Bold the column headings

Excel.Range oRange = (Excel.Range)oSheet.Cells[_RowIndex, _ColIndex];

oRange.Font.Bold =true;

}

|||You could execute a query, and pull them from the SysColumns tables:
Select
SC.Name
From Syscolumns SC
inner join systypes ST on SC.xtype=ST.xtype
Where OBJECT_NAME(SC.id) = 'MyTableName'

|||Do i use the query as usaul commandtext?
because i get error massage- "invalid object name SC"|||

If you are using sql statements it is recommended that you use the schema views in the database instead of the sysobject tables directly. Here is an example that will output all of the table information for you(using the Northwind database):

declare @.tablename varchar(255)

select @.tablename = 'Employees'

SELECT INFORMATION_SCHEMA.COLUMNS.*,
(SELECT COLUMNPROPERTY(OBJECT_ID(@.tablename),
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsComputed')) AS IsComputed,
(SELECT COL_LENGTH(@.tablename, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME)) AS ColumnLength,
(SELECT COLUMNPROPERTY(OBJECT_ID(@.tablename), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsIdentity')) AS IsIdentity,
(SELECT COLUMNPROPERTY(OBJECT_ID(@.tablename), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsRowGuidCol')) AS IsRowGuidColumn,
(ISNULL((SELECT TOP 1 CASE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN 1 END
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME=INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME
WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = @.tablename AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY'), 0)) AS IsPrimaryKey,
(ISNULL((SELECT TOP 1 CASE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE WHEN 'FOREIGN KEY' THEN 1 END
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME=INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME
WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME=@.tablename AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY'), 0)) AS IsForeignKey,
(ISNULL((SELECT TOP 1 CASE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE WHEN 'UNIQUE' THEN 1 END
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME=INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME
WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = @.tablename
AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'UNIQUE'), 0)) AS HasUniqueConstraint
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.tablename

|||Can you show me the code you used to execute the query? I'll check it for ya.
Thanks,
Tyler
|||this is the code:

PrivateSub settablecolumn()

Dim cmdAsNew SqlCommand()

Dim firstcolumnAsString

If oConnection.State = ConnectionState.ClosedThen oConnection.Open()

cmd.Connection = oConnection

cmd.CommandText = "Select L.Name() From Syscolumns L inner join systypes R on L.xtype=R.xtype Where OBJECT_NAME(L.id) = '" & table.SelectedItem.Value & "'"

Dim readerAs SqlDataReader = cmd.ExecuteReader

If reader.Read =TrueThen

firstcolumn = reader.Item(0)

EndIf

oConnection.Close()

EndSub
great thanks...

|||Should work if you remove the parentheses from Name:
cmd.CommandText = "Select L.Name From Syscolumns Linner join systypes R on L.xtype=R.xtype Where OBJECT_NAME(L.id) = '"& table.SelectedItem.Value & "'"
If not, let me know what the text of the Error message is.
Thanks,
Tyler

|||working great now, thank you very much.