Query to get a list of field/column names with the tablm name.

Thanks for reading my question.

I need some help on a query which will provide me with the following information.

TableName ColumName

Table1 Column1

Table1 Column2

Table1 Column3

Table2 Column1

Table2 Column2

Table2 Column3

Table2 Column1

Table3 Column1

Table3 Column2

Thanks.

I got it...

SELECT dbo.sysobjects.name AS TableName, dbo.syscolumns.name AS

ColumnName, dbo.systypes.name AS DataType, dbo.syscolumns.length,

dbo.syscolumns.xprec, dbo.syscolumns.xscale,

dbo.syscolumns.colid AS ColumnSort, dbo.sysindexkeys.colid

FROM dbo.sysobjects INNER JOIN

dbo.syscolumns ON dbo.sysobjects.id =

dbo.syscolumns.id INNER JOIN

dbo.systypes ON dbo.syscolumns.xusertype =

dbo.systypes.xusertype LEFT OUTER JOIN

dbo.sysindexkeys ON dbo.syscolumns.colid =

dbo.sysindexkeys.colid AND dbo.syscolumns.id = dbo.sysindexkeys.id

WHERE (dbo.sysobjects.xtype = 'U')

|||

Thanks for following up your own post. It prevents others from wasting their time trying to help you after you have solved the problem, and it helps others when you share your solution.

|||

use information_schema.columns

Code Snippet

select table_name,column_name from information_schema.columns

order by table_name,ordinal_position

|||

select Table_catalog as Databasename, table_name, column_name from INFORMATION_SCHEMA.COLUMNS

|||

DON'T write your query against the SYSTEM tables, the schema might change without any notice. Always trust with information_schema.xxxx views.

I do like to play with system tables, but when it come to solid solutions, we have to stick with few coditions. |||

Sorry for that unnecessary answer from me. Arnie/Mani and Team are really quick to answer any question. When i open this thread nobody was answered. But when i completed typing there were two reply... Really quick buddies...

g8 keep it up

Madhu

|||

Madhu,

Sometimes that happens this time of day. For slower responses, and a longer 'window of opportunity, try about four hours later in the day.