Anton Gochev’s Weblog

I’d like to share with people some tables which I found useful for retrieving information about database schema of a database created using SQL Server CE.

In order to access information schema of database you should use a prefix named information_schema. Tables I use to access such information are:

• tables - contains information about tables which belongs to opened database;
• columns - contains all columns’ information;
• referential_constraints - contains information about foreign keys, constraint names, constraint table names and PK constraint table names, update/delete rules etc;
• table_constraints - describes table constraints if any connected with a table;
• key_column_usage - shows primary key columns which have a constraint.

Sample:

select * from information_schema.tables;
select * from information_schema.columns;
select * from information_schema.referential_constraints;
select * from information_schema.key_column_usage;
select * from information_schema.table_constraints;

Tags: SQL CE