2

Knowing to use

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

to query for a list of tables, I'm currently finding no way to retrieve a list of non-system tables only.

I.e. the above query also lists me system tables like "sysdiagrams" or "dtproperties".

So my question is:

How to query a database for existance of any user tables?

3

The following from sys.tables will work. is_ms_shipped defines whether or not the object is a Microsoft table, however sys.tables should contain only user-defined tables

SELECT * FROM sys.tables WHERE is_ms_shipped = 0

To exclude sysdiagrams from the list, here is something I've borrowed from Aaron Bertrand on this forum

SELECT o.*
FROM sys.objects o
LEFT OUTER JOIN
sys.extended_properties ep
ON o.[object_id] = ep.major_id
AND ep.class_desc = 'OBJECT_OR_COLUMN'
AND ep.[name] = 'microsoft_database_tools_support'
WHERE
o.is_ms_shipped = 0
AND ep.major_id IS NULL 
AND type = 'U';

You can also check out this answer on Stack Overflow

  • Just tested on a newly created database. The table "sysdiagrams" also appears in that collection (is_ms_shipped is "0"). So this seems to not work for me. – Uwe Keim Mar 10 '14 at 10:35
  • 2
    Ah, sysdiagrams is technically not ms_shipped and is a table you create yourself on the database when you create a new DB diagram. You have to join to sys.extended_properties to exclude those tables. I've updated my answer above. – Mark Sinkinson Mar 10 '14 at 10:42
  • Thanks! Your solution works better than the SO one, since yours also excludes the dtproperties table. – Uwe Keim Mar 10 '14 at 10:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.