getting a list of user created tables ONLY

2 Sep 2005 2:48 PM

kevin

sql server 2k

I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
each case I also get a table called dtproperties and, in neither case, is
there a logical way to tell one apart. I am also adverse to using
undocumented system tables seeing as sql server 2005 is just around the
corner and upgrading is more than likely... and its a bad idea.

I am currently using the following. Isn't there a more built in way to do
this?

SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'

2 Sep 2005 3:00 PM

B@DJJ

Here's one way...

--Get all the dbo-owned Tables together and exclude system, view, and tables
begining with 'ARCH_' (Archive tables)
Create table #IntermediateTableList
    (Table_Qualfier varchar(100),
    Table_Owner varchar(100),
    Table_Name varchar(100),
    Table_Type varchar(100),
    Remarks varchar(100),
    Table_Count numeric(9))

--Create table #IntermediateTableList (Table_Name varchar(100), Table_Count
numeric(9))
Insert into #IntermediateTableList (Table_Qualfier, Table_Owner, Table_Name,
Table_Type, Remarks) Execute sp_Tables

--Exclude non-dbo-owned tables, system tables, views, and tables begining
with 'ARCH_' (Archive tables)
Select Table_Name, Table_Count into #FinalizedTableList from
#IntermediateTableList where (Table_Type <> 'system table' and Table_Type <>
'view' and Table_Name NOT LIKE 'ARCH_%' and TABLE_OWNER = 'dbo')

Show quote

"kevin" wrote:

> sql server 2k
>
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
>
> I am currently using the following. Isn't there a more built in way to do
> this?
>
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'

2 Sep 2005 3:04 PM

Alejandro Mesa

See view information_schema.tables and function objectproperty.

Example:

use northwind
go

select
    *
from
    information_schema.tables
where
    table_type = 'base table'
    and objectproperty(object_id(quotename(table_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
    and objectproperty(object_id(quotename(table_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
go

AMB

Show quote

"kevin" wrote:

> sql server 2k
>
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
>
> I am currently using the following. Isn't there a more built in way to do
> this?
>
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'

2 Sep 2005 3:32 PM

kevin

Thanks to the two of you.

Alejandro, that was the ticket. Gracias!!

Show quote

"Alejandro Mesa" wrote:

> See view information_schema.tables and function objectproperty.
>
> Example:
>
> use northwind
> go
>
> select
>     *
> from
>     information_schema.tables
> where
>     table_type = 'base table'
>     and objectproperty(object_id(quotename(table_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
>     and objectproperty(object_id(quotename(table_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> go
>
>
> AMB
>
> "kevin" wrote:
>
> > sql server 2k
> >
> > I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> > each case I also get a table called dtproperties and, in neither case, is
> > there a logical way to tell one apart. I am also adverse to using
> > undocumented system tables seeing as sql server 2005 is just around the
> > corner and upgrading is more than likely... and its a bad idea.
> >
> > I am currently using the following. Isn't there a more built in way to do
> > this?
> >
> > SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> > FROM INFORMATION_SCHEMA.TABLES
> > WHERE table_type = 'base table'     AND TABLE_NAME <> 'dtproperties'