SQL SERVER – CE – List of Information_Schema System Tables

Yesterday I wrote  blog post that I downloaded WebMatrix and it was very easy to install, after installing I noticed it has default database as SQL CE. I started to play with SQL CE and I was glad that it supports many of the Information_Schema. There is one important thing I need to mention. Yesterday I shared Sample Database of the SQL CE. Few of the readers tried to install that database in other versions and it give them error. Please note that SQL CE will only and will not work with any other version of the database.

Here are few of the Information_schema system tables which works in SQL CE.

-- Get all the columns of the database
SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS
-- Get all the indexes of the database
SELECT * 
FROM INFORMATION_SCHEMA.INDEXES
-- Get all the indexes and columns of the database
SELECT * 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
-- Get all the datatypes of the database
SELECT * 
FROM INFORMATION_SCHEMA.PROVIDER_TYPES
-- Get all the tables of the database
SELECT * 
FROM INFORMATION_SCHEMA.TABLES
-- Get all the constraint of the database
SELECT * 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
-- Get all the foreign keys of the database
SELECT * 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Here is my question – how many of you use SQL CE and how many of you use SQL Express? and for what purpose?

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)