List Columns in a Table in SQL 2005

Hi, I know sys.tables and sys.columns gives me a list of tables and
columns in a SQL 2005 database.

How can I list Columns in a specific Table please?

Thanks in advance,
Ronny

Re: List Columns in a Table in SQL 2005

You have to join both catalog views by object_id:

SELECT SCHEMA_NAME(T.schema_id) AS 'Schema',
T.name AS 'Table Name',
C.name AS 'Column Name'
FROM sys.tables AS T
JOIN sys.columns AS C
ON T.object_id = C.object_id
WHERE T.type = 'U'
AND T.name = 'MyTableName';

Or you can use:

SELECT table_schema,
table_name,
column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'MyTableName';


HTH,

Plamen Ratchev
http://www.SQLStudio.com

Re: List Columns in a Table in SQL 2005

ronn2007@hotmail.co.uk wrote:
> Hi, I know sys.tables and sys.columns gives me a list of tables and
> columns in a SQL 2005 database.
>
> How can I list Columns in a specific Table please?
>
> Thanks in advance,
> Ronny
desc tablename

Re: List Columns in a Table in SQL 2005

lark wrote:
> ronn2007@hotmail.co.uk wrote:
>> Hi, I know sys.tables and sys.columns gives me a list of tables and
>> columns in a SQL 2005 database.
>>
>> How can I list Columns in a specific Table please?
>>
>> Thanks in advance,
>> Ronny
> desc tablename
scratch that. it doesn't work in ms sqlserver.