Finding user tables in database

23 Dec 2005 4:12 AM

Mana

Hi,

I am using SQL Server 2005.I need to find out all the user tables in a
database. For this purpose I have written following query. NSBank is
the name of the database.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
USE NSBank;
select name, type_desc from sys.objects where type='u';
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
When I execute this, I get the names of all the user tables along with
the table 'sysdiagrams' which is a system table, created as a result of

relationships between other tables in the database.

Also tried following query. It works fine in SQL Server 200 but not in
SQL Server 2005.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

How should I modify my query to omit the 'sysdiagrams' table from the
result set?

Thanks,
Mana

23 Dec 2005 5:22 AM

SriSamp

Just exclude it by using a NOT IN clause.

--
HTH,
SriSamp
Email: sris***@gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp

Show quote

"Mana" <DearMan***@gmail.com> wrote in message
news:1135311132.437645.42600@g43g2000cwa.googlegroups.com...
> Hi,
>
> I am using SQL Server 2005.I need to find out all the user tables in a
> database. For this purpose I have written following query. NSBank is
> the name of the database.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> USE NSBank;
> select name, type_desc from sys.objects where type='u';
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> When I execute this, I get the names of all the user tables along with
> the table 'sysdiagrams' which is a system table, created as a result of
>
> relationships between other tables in the database.
>
> Also tried following query. It works fine in SQL Server 200 but not in
> SQL Server 2005.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> SELECT * FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> How should I modify my query to omit the 'sysdiagrams' table from the
> result set?
>
>
> Thanks,
> Mana
>

23 Dec 2005 7:36 AM

Jens

What about:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0

HTH, jens Suessmeyer.

23 Dec 2005 8:32 AM

SriSamp

That's what I thought of using initially, but if you query sys.tables, it
actually shows the is_ms_shipped column as '0' for this table also.

--
HTH,
SriSamp
Email: sris***@gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp

Show quote

"Jens" <J***@sqlserver2005.de> wrote in message
news:1135323388.691619.102520@g43g2000cwa.googlegroups.com...
> What about:
>
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0
>
> HTH, jens Suessmeyer.
>