syscolumns and sysobjects 1

[mkey]

Hi All,
In oracle I could get the column name of a table by using user_tab_columns which will give me the column name and corresponding table name. How can I do this in sql server?

thanks

 

[Discord]

Select A.Name AS TableName, B.Name AS ColumnName
FROM SysObjects AS A JOIN 
SysColumns AS B ON A.ID = B.ID
WHERE A.XType = 'U'

will give you the table name and all the column names for all the user tables. is this anything like what you were looking for?

 

[mkey]

That's exactly what I wanted. Thank you so very much for your prompt response.

 

[fluteplr]

If you are running SQL 7 or SQL 2000 a better way to do this is by using the information_schema views.

The one you would use in this case is.

information_schema.columns.

Microsoft does not suggest using the sys files directly (although we all do it) since the structures may change.

They do say that the information_schema views will not change (with the possible exception of additional fields), so I have been trying to use those where possible.

 

[Discord]

cool! thanks for the info fluteplr