Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
| Search | Today's Posts | Mark Forums Read
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 6th, 2004, 11:31 PM
Authorized User
 
Join Date: Aug 2004
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default Retrieving list of tables inside a database...

Hi every body
  I want to know if there is a SQL query in ASP.NET programming by which you can retrieve names of tables inside a database?

 
Old September 11th, 2004, 09:17 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Assuming that you are using SQL server...

Here it is. This gives you the list of tables from a database.
Code:
SELECT name FROM sysobjects WHERE type="U"
Cheers!

_________________________
- Vijay G
Strive for Perfection
 
Old September 13th, 2004, 12:18 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cochin, Kerala, India.
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default

Hello Vijay,

I also had a similar doubt. Thanks for the reply.

But there is a small problem. The above query returns a table dtproperties. Infact, I have not created such a table in my database. I found such a table is there in all the databases on my local server. May be a system created one.

My problem is that is this the only table that will be not created by me and returned in the above query ? If yes, I can exclude it from the list. Will there be any other system created tables that will be returned by the above query ?

Thanks

Madhu
 
Old September 13th, 2004, 06:16 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Madhu,

Database diagrams are stored in the 'dtproperties' table within the database. It is actually server created table. In case you want to ignore that use a condition <> that table.
Code:
SELECT name FROM sysobjects WHERE type='U' and name <> 'dtproperties'
Cheers!

_________________________
- Vijay G
Strive for Perfection
 
Old September 13th, 2004, 08:36 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cochin, Kerala, India.
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default

So, I am assuming that dtproperties is the only system created table that will be returned by the query

SELECT name FROM sysobjects WHERE type='U'

Thanks for this info.

Madhu
 
Old September 13th, 2004, 10:26 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Not for sure, but it seems to return a Negative value in column "status". Seems to be the same in all dbs.



Sal
 
Old September 13th, 2004, 10:47 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Yes Sal,

I too observed that, which has got Status as SAME negative value on all databases on a server. I have read it in MS site that I contains the ER diagram info pertaining to that DB where it resides, and exporting this to another similar DB would result in transfering the diagram too. Also read about users who are unable to create diagrams, should have relavant permissions on this table. So I am sure it is system created table, when one tries to relate a table with another.

1) I created a New DB (no dtproperties by default)
2) Created 2 new tables (no dtproperties by default still)
3) Created a diagram, to relate those 2 tables. (Found dtproperties table created in the db now with some rows in it) But can't make you what those rows mean, form the data stored in it.

Cheers!

_________________________
- Vijay G
Strive for Perfection




Similar Threads
Thread Thread Starter Forum Replies Last Post
Retrieving Primary Key columns from system tables canasdaq SQL Server 2000 1 November 28th, 2006 11:23 PM
Retrieving the list of tables from a database devi.g@india.com J2EE 0 February 2nd, 2005 09:54 PM
Appending new nodes inside tables in IE 6 stephen_c_ Javascript How-To 1 January 26th, 2005 05:06 AM
Query database for List of tables & other details imsuneeta SQL Server 2000 2 January 18th, 2005 07:48 AM
servlet retrieving a list bessie2003 Servlets 0 June 22nd, 2003 08:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.