get user tables/views and/or system tables/views

[David Thielen]

Hi;

I use:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE (table_type =
'BASE TABLE') and (TABLE_NAME != 'dtproperties')

to get all user tables. Is this still the best way to do this?

And what is the select to get all system tables?

thanks - dave


david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

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

 

[Trevor Benedict]

David,
Have you tried "SQL Profiler". This is by far one of the best tools to dig
around and see how SQL Server in itself builds the SQL.

All user defined Tables
Select * from sysobjects
where xtype = 'U'

This should give you a fair idea about the sysobjects table

http://articles.techrepublic.com.com/5100-10878_11-6142579.html

Regards,

Trevor Benedict

 

[David Thielen]

thank you very much.

New question, how can I get the system tables & views?

thanks - dave

David,
Have you tried "SQL Profiler". This is by far one of the best tools to dig
around and see how SQL Server in itself builds the SQL.

All user defined Tables
Select * from sysobjects
where xtype = 'U'

This should give you a fair idea about the sysobjects table

http://articles.techrepublic.com.com/5100-10878_11-6142579.html

Regards,

Trevor Benedict

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

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

 

[Trevor Benedict]

David,
This article explains what the values for x_type represent. Using this you
should be able to build your query

http://www.devx.com/tips/Tip/14107

Regards,

Trevor Benedict