CODEWITHSUNDEEP
sqlsql-serversql-server-2000
Sonny Boy
Sonny Boy

Reputation: 8016

Get list of tables but not include system tables (SQL Server 2K)?

I know I can get a list of tables from a given database with the following query:

select *
from information_schema.tables

How do I go about excluding system tables though?

Upvotes: 7

Views: 19996

Answers (6)

jacouh
jacouh

Reputation: 8741

I use this since SQL Server Express 2022:

SELECT name FROM sys.tables
WHERE is_ms_shipped = 0 AND name <> 'sysdiagrams'

This excludes both dbo.dtproperties and dbo.sysdiagrams tables.

Upvotes: 0

MIWMIB
MIWMIB

Reputation: 1497

    SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME NOT LIKE 'sys%'

Not an elegant solution but this was my quick way to exclude dbo.sysdiagrams, which was the only system table in my list.

Upvotes: 2

BlueWolfy
BlueWolfy

Reputation: 65

I know this is quite an old question too, but you can execute the sql server stored procedure :

EXEC sp_tables @table_name="%", @table_owner="%",@table_type="'TABLE'"

and retrieve your table list. But is not supported with SQL 2K. Exists since the 2005 server...

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239714

I know this is quite an old question, but someone's just edited it to resurrect it, and the "right" answer from my perspective isn't either of the two listed. The accepted answer includes some "system" tables (dtproperties is mentioned in the comments. If the user had any replication going on, they'd have found a few more).

The other answer uses a 2005 table, but is so nearly correct. For 2000, you want to use OBJECTPROPERTY instead:

select name from sysobjects where
    OBJECTPROPERTY(id,N'IsTable')=1 and
    OBJECTPROPERTY(id,N'IsMSShipped')=0

Upvotes: 10

Denaem
Denaem

Reputation: 587

SELECT name FROM [database].sys.tables where is_ms_shipped=0

Upvotes: 5

heisenberg
heisenberg

Reputation: 9759

select name from sysobjects where type='U'

Upvotes: 6

Related Questions