Exclude system tables from table list

[djj55]

Hello, SQL 2008R2
I would like to get a list of tables without the system tables within the database.

I execute

Select Table_name as "Table name"
From Information_schema.Tables
Where Table_type = 'BASE TABLE' and Objectproperty(Object_id(Table_name), 'IsSystemTable') = 0

which gives me all my tables but also gives the table "sysdiagrams", which in Object Explorer is under Tables - System Tables.

I have also tried

Objectproperty(Object_id(Table_name), 'IsMSShipped') = 0

as it was in the original example.

Thanks,


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[simian336]

Hey DJ,

I don't see the same thing, so I was wondering could you

select * from sys.tables

is it defines as USER_TABLE there?

Thanks

Simi

 

[djj55]

Thanks for the reply.

Simi, I ran the select and the type of the table is 'U', however there is one difference in that the lob_data_space_id = 1 for the table "sysdiagrams" and zero for the others.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

simian,

sysdiagrams is a table that is created whenever you expand the "Database Diagrams" node in SQL Server Management Studio. With SQL2000, the table was names 'dtproperties'.

there is one difference in that the lob_data_space_id = 1 for the table "sysdiagrams" and zero for the others.

You shouldn't use the lob_data_space_id as an indicator for a system table, because it's not. Not even close. This basically identifies where the system stores "out of row" data.

According to books on line:

lob_data_space_id:

A nonzero value is the ID of the data space (filegroup or partition scheme) that holds the text, ntext, and image data for this table.

0 = The table does not contain text, ntext, or image data.

I hate to say it, but those 2 tables are not marked in any way as system tables (even though I think they should be). I would suggest you just hard code those tables, like this:

Select Table_name as "Table name"
From   Information_schema.Tables
Where  Table_type = 'BASE TABLE' 
       and Table_Name Not In ('dtproperties','sysdiagrams')

I hate hard coding data like this, but I think this is one of those situations where it is necessary.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thanks George,
I was looking for a way to not code around various tables (be it hard code or table with exceptions). To get the information I wanted I found an example from Pinal Dave to get a list using sp_msforeachdb.

So for now I have come up with

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ListOfTables]') AND type in (N'U'))
    DROP TABLE [dbo].[ListOfTables]

CREATE TABLE ListOfTables (DBName VARCHAR(250), TName VARCHAR(250))

INSERT INTO ListOfTables 
EXECUTE sp_msforeachdb 'select "?" AS DBName, Name AS TName from [?].sys.tables'

-- Use TINYINT so groups my be created
ALTER TABLE ListOfTables 
    ADD OK2Use TINYINT NOT NULL DEFAULT 1

UPDATE ListOfTables 
SET OK2Use = 0
WHERE DBName IN ('master', 'tempdb', 'model', 'msdb', 'ReportServer$DB1SQL', 
            'ReportServer$DB1SQLTempDB', 'SQLdmRepository')
    OR TName IN ('sysdiagrams', 'dtproperties')

UPDATE ListOfTables 
SET OK2Use = 2
WHERE DBName IN ('Temp_DB1', 'Temp_DB2') 
    AND OK2Use = 1

There are ways to improve this but this was my first cut.

Thanks again Simi and George

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!