Retrieving list of tables inside a database...

vb_developer · September 6th, 2004, 11:31 PM

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?

happygv · September 11th, 2004, 09:17 AM

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

madhukp · September 13th, 2004, 12:18 AM

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

happygv · September 13th, 2004, 06:16 AM

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

madhukp · September 13th, 2004, 08:36 AM

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

sal · September 13th, 2004, 10:26 AM

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

Sal

happygv · September 13th, 2004, 10:47 AM

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