MS SQL Server Notification Services - Finding out user tables in database

Author

Finding out user tables in database

Mana

2005-12-22, 3:24 am

Hi,

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. How should I modify
my query to omit this table from the result set?

Thanks,
Mana

Joe Webb

2005-12-22, 9:23 am

Hi Mana-

Use this statement instead:

SELECT
*
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE'

BTW - a better group would be microsoft.public.sqlserver.programming.
This on is dedicated to SQL Server Notification Services.

HTH...

--
Joe Webb
SQL Server MVP
http://www.sqlns.com

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

On 21 Dec 2005 20:28:10 -0800, "Mana" <DearManasi@gmail.com> wrote:

>Hi,
>
>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. How should I modify
>my query to omit this table from the result set?
>
>Thanks,
>Mana

Mana

2005-12-23, 3:24 am

Hi Joe,

Thanks...I ll take care that I post only queries related to NS here :)

Mana

Sponsored Links