Tuesday, February 12, 2013

SQL Server Compact Code Snippet of the Week #6 : list all user tables in a database

This week’s short code snippet shows how to list all the user table objects in a SQL Server Compact database file. Notice that a SQL Server Compact database can also contain a number of system tables, these can be created by the Merge Replication/RDA/Sync Framework APIs. The metadata (like list of tables) is not exposed as tables, but as something called views, specifically the INFORMATION_SCHEMA views (despite the fact that SQL Server Compact does not support user defined views).

SELECT table_name AS Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE <> N'SYSTEM TABLE'

2 comments:

wb5rvz said...

I left out the table_type qualification and the query yielded the same results (SQL CE 4.0)

If I query for table_type = 'system table' I get no rows

ErikEJ said...

Sure - your particular database does apparantly not have any system tables (they are created by for example the replication APIs)