Fields on the table

What database type is being used? (the syntax differs a LOT between db types)

please note the topic "SQL" refers to the generic standardized language;  not to any specific vendor's product such as "Microsoft SQL Server"

For a table of columns and their types...
In Oracle:

SELECT
  *
FROM
  DBA_TAB_COLUMNS
WHERE
  OWNER = '[schemaname]'
  AND TABLE_NAME = '[tablename]'
ORDER BY
  COLUMN_ID;

Select allOpen in new window

In Microsoft SQL Server:

SELECT
	[sys].[columns].*
FROM
	[sys].[columns]
INNER JOIN
	[sys].[tables] ON
	[sys].[tables].[object_id] = [sys].[columns].[object_id]
INNER JOIN
	[sys].[schemas] ON
	[sys].[schemas].[schema_id] = [sys].[tables].[schema_id]
WHERE
	[sys].[schemas].[name] = '[schemaname]'
	AND [sys].[tables].[name] = '[tablename]'
ORDER BY
	[column_id]

Select allOpen in new window

If you're using something other than one of those two you'll need to specify the database platform being used.

Hi experts, I am using Microsoft SQL Server 2008

Then the 2nd query in my above example will give you what you want.

Thanks PortletPaul

Hi Russ Suter,
I wish to use this approach, Set rs = cn.Execute("Select * from DTR") and obtain the list of fields inside DTR. Can you help me what are the next commands should I use to achieve the goal?

Hi Russ Suter, Thank you for providing me such code. It's actually working but what I want here is the tables name in the DTR. I want to apply maybe loop so that I could get the existing table in DTR. Hope you can extend your help. Thanks!

This isn't making sense. What is 'DTR'? Is it a table, a schema, a database?

If it's a table then it has fields (or columns). If it's a schema then it has tables. If it's a database then it has a whole bunch of things.

Sorry Russ Suter, DTR is a Table and I want to get the all columns name on it.

I've already answered that question.

SELECT
	[sys].[columns].*
FROM
	[sys].[columns]
INNER JOIN
	[sys].[tables] ON
	[sys].[tables].[object_id] = [sys].[columns].[object_id]
	AND [sys].[tables].[name] = 'DTR'
ORDER BY
	[column_id]

Select allOpen in new window

What am I missing?

Hi Russ, your code is working very well. The way I applied your code is something like this;

   Set rs = cn.Execute("Select [sys].[columns].* FROM [sys].[columns] INNER JOIN [sys].[tables] ON [sys].[tables].[object_id] = [sys].[columns].[object_id] AND [sys].[tables].[name] ='" & "DTR" & "' ORDER BY [column_id] ")      

         with rs
            If rs.BOF = True Then 'Not exist
               response.write("Not exist")
               response.end()
               cn.close
            else
               response.write (trim(rs("column_name"))
               response.end()
               cn.close
            end if	
         end with

Select allOpen in new window

Base on above, I want to obtain columns name. Thank you!

Replace "column_name" with "name" in your code

Also, if all you're interested in is the name of the column you an replace the '*' in the query with "name"

Thanks a lot, Russ Suter, It's now working...