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

Question

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?

Persia

Answer 1

select name from sysobjects where type='U'

Mellott

Doesn't seem to work as I expect it to. One of the tables that show up under the System Tables folder in Management Studio, "dtproperties", also shows up in this query and my original. How/why is Management Studio identifying it as a system table? – Persia

dtproperties stores the data diagram information I believe, I guess its technically classified as a user table. – Mellott

Thanks. So I guess I just write my query to specifically ignore that table or there a "cleaner" way around it? – Persia

If that's the only table giving you problems then I don't think it'd be too much of a coding horror to just write the query to specifically ignore it. – Mellott

It is the only one at the moment, I just wanted to make sure there wasn't any other potential problems in the future. – Persia

Not that I'm aware of, but then again I wasn't aware that dtproperties was considered a user table either :) I think that is the best that you can do though, the type flag is intended to be used for differentiating between system and user but this is a special case where a management studio designer is generating a table so from MSSQL's perspective its not a system table, as I understand it. – Mellott

Answer 2

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

Abeyance

This includes dbo.sysdiagrams – Appendicectomy

Answer 3

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

Appendant

Sorry, sys.tables is not supported in SQL Server 2000. – Persia

Answer 4

    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.

Cherellecheremis

Answer 5

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...

Froggy

Answer 6

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.

Appendicectomy