List Columns in a Table in SQL 2005

[ronn2007@hotmail.co.uk]

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

[Plamen Ratchev]

Re: List Columns in a Table in SQL 2005

You have to join both catalog views by object_id:

SELECT SCHEMA_NAME(T.s chema_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_SCH EMA.COLUMNS
WHERE table_name = 'MyTableName';


HTH,

Plamen Ratchev

http://www.SQLStudio.com

[lark]

Re: List Columns in a Table in SQL 2005

ronn2007@hotmai l.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

[lark]

Re: List Columns in a Table in SQL 2005

lark wrote:

ronn2007@hotmai l.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.