Fields on the table

Hi experts, I wish to know how can I retrieve the existing field in a certain table. I have an existing table named DTR, I want to know what are the fields inside on it by using sql commands. Is it possible?
Whing Dela CruzAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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"
Russ SuterSenior Software DeveloperCommented:
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;

Open 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]

Open in new window

If you're using something other than one of those two you'll need to specify the database platform being used.
Whing Dela CruzAuthor Commented:
Hi experts, I am using Microsoft SQL Server 2008
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Russ SuterSenior Software DeveloperCommented:
Then the 2nd query in my above example will give you what you want.
Whing Dela CruzAuthor Commented:
Thanks PortletPaul
Whing Dela CruzAuthor Commented:
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?
Russ SuterSenior Software DeveloperCommented:
This is beyond the scope of your original question. It seems now you're asking for help with Visual Basic. The query I mentioned above would work. If you wanted to simplify it you could eliminate the schema part. The slightly simplified query would look like this:
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]

Open in new window

If you wanted to do this in VB then I'd need to know what data type rs is in your above example. Most likely, rs will have a Columns collection and you could iterate through that.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Whing Dela CruzAuthor Commented:
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!
Russ SuterSenior Software DeveloperCommented:
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.
Whing Dela CruzAuthor Commented:
Sorry Russ Suter, DTR is a Table and I want to get the all columns name on it.
Russ SuterSenior Software DeveloperCommented:
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]

Open in new window

What am I missing?
Whing Dela CruzAuthor Commented:
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

Open in new window

Base on above, I want to obtain columns name. Thank you!
Russ SuterSenior Software DeveloperCommented:
Replace "column_name" with "name" in your code
Russ SuterSenior Software DeveloperCommented:
Also, if all you're interested in is the name of the column you an replace the '*' in the query with "name"
Whing Dela CruzAuthor Commented:
Thanks a lot, Russ Suter, It's now working...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.