How do I enum all system tables in a database - and more???

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is for Sql Server.

"SELECT table_name FROM information_schema.tables WHERE (table_type = 'BASE
TABLE') ORDER BY table_name";

gives me all user tables plus the system dtproperties.

1) How can I get just user tables (ie no dtproperties also)?
2) How can I get just system tables?
3) How can I get all tables (user & system)?
4) How can I get the metadata description for the table?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Hi David,

Try using SqlConnection.GetSchema - i think it will give you more
information.
 
Hi;

We do that for ODBC and OleDb but I've found it's hit or miss even there and
so I think information_schema.tables is safer.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm




Miha Markic said:
Hi David,

Try using SqlConnection.GetSchema - i think it will give you more
information.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

David Thielen said:
This is for Sql Server.

"SELECT table_name FROM information_schema.tables WHERE (table_type =
'BASE
TABLE') ORDER BY table_name";

gives me all user tables plus the system dtproperties.

1) How can I get just user tables (ie no dtproperties also)?
2) How can I get just system tables?
3) How can I get all tables (user & system)?
4) How can I get the metadata description for the table?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm
Click to expand...
Click to expand...
 
Hi Dave,
1) How can I get just user tables (ie no dtproperties also)?
Click to expand...
select * from sysobjects where type='U'
(U - User Table)
2) How can I get just system tables?
Click to expand...
select * from sysobjects where type='S'
(S - System Table)
3) How can I get all tables (user & system)?
Click to expand...
select * from sysobjects where type='S'or type='U'
4) How can I get the metadata description for the table?
Click to expand...
sp_help "dbo.TableName"

Hope this helps, please let me know if you have any further issue on this.
Have a great day,
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hi;

That select has the same problem as the SELECT table_name FROM
information_schema.tables WHERE (table_type = 'BASE TABLE') select - it
returns dtproperties as a user table.

I tried the sp_help [dbo.Orders] but no description - just name, owner,
type, and created_datetime. (I'm on Sql Server 2000).

Any other possibilities?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Hi Dave,
Thank for your reply,

The dbo.dtproperties table is the one we create when a database diagram is
created. SQL 2000 Enterprise manager shows it as a system table. However,
the type column of it in sysobjects is 'U' rather than 'S'. This is
because, enterprise will check the 'IsMSShipped' property of an object, if
it is 1, then it is also system object. This table(dtproperties) has been
removed after SQL 2005.
For example:
select objectproperty( object_id('[dbo].[dtproperties]'), 'IsMSShipped')

If you use sqlconnection.getschema("tables") in ADO.net to get all the
tables information from SQL 2000, it will also return dtproperties(table) .
This is a special table in SQL 2000, however, if you want to filter this
table said:
I tried the sp_help [dbo.Orders] but no description - just name, owner,
type, and created_datetime. (I'm on Sql Server 2000).
Click to expand...

sp_help [dbo.Orders] will return eight RecordSets.
Table contained "Name, owner, type" column is the first recordset return by
this method. For description of table, you can find it from the second
recordset. You may execute this method in SQL 2000 query analyzer. It will
show you the whole story.

The article as below provide the detailed information about this method in
SQL2005. However, it will also work on your SQL 2000.
http://msdn2.microsoft.com/en-us/library/ms187335.aspx
[sp_help (Transact-SQL) ]

Hope this helps. Please let me know if you still have any further issue on
it. I'm glad to assist you.
Have a great day,
Sincerely,
Wen Yuan
Microsoft Online Community Support
 
Hi Dave,
Sorry, I need correct somthing.
however, if you want to filter this table, you can add case section such
Click to expand...
as "where name<>'dtproperties'".

Have a great day,
Sincerely,
Wen Yuan
Microsoft Online Community Support
 
Back
Top