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 (http://blog.SQLAuthority.com)

7 thoughts on “SQL SERVER – CE – List of Information_Schema System Tables

  1. –Drop and Recreate Foreign Key Constraints

    SET NOCOUNT ON

    DECLARE @table TABLE(
    RowId INT PRIMARY KEY IDENTITY(1, 1),
    ForeignKeyConstraintName NVARCHAR(200),
    ForeignKeyConstraintTableSchema NVARCHAR(200),
    ForeignKeyConstraintTableName NVARCHAR(200),
    ForeignKeyConstraintColumnName NVARCHAR(200),
    PrimaryKeyConstraintName NVARCHAR(200),
    PrimaryKeyConstraintTableSchema NVARCHAR(200),
    PrimaryKeyConstraintTableName NVARCHAR(200),
    PrimaryKeyConstraintColumnName NVARCHAR(200)
    )

    INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
    SELECT
    U.CONSTRAINT_NAME,
    U.TABLE_SCHEMA,
    U.TABLE_NAME,
    U.COLUMN_NAME
    FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
    ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
    WHERE
    C.CONSTRAINT_TYPE = ‘FOREIGN KEY’

    UPDATE @table SET
    PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
    FROM
    @table T
    INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
    ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

    UPDATE @table SET
    PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
    PrimaryKeyConstraintTableName = TABLE_NAME
    FROM @table T
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
    ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

    UPDATE @table SET
    PrimaryKeyConstraintColumnName = COLUMN_NAME
    FROM @table T
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
    ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

    –SELECT * FROM @table

    –DROP CONSTRAINT:
    SELECT

    ALTER TABLE [‘ + ForeignKeyConstraintTableSchema + ‘].[‘ + ForeignKeyConstraintTableName + ‘]
    DROP CONSTRAINT ‘ + ForeignKeyConstraintName + ‘

    GO’
    FROM
    @table

    –ADD CONSTRAINT:
    SELECT

    ALTER TABLE [‘ + ForeignKeyConstraintTableSchema + ‘].[‘ + ForeignKeyConstraintTableName + ‘]
    ADD CONSTRAINT ‘ + ForeignKeyConstraintName + ‘ FOREIGN KEY(‘ + ForeignKeyConstraintColumnName + ‘) REFERENCES [‘ + PrimaryKeyConstraintTableSchema + ‘].[‘ + PrimaryKeyConstraintTableName + ‘](‘ + PrimaryKeyConstraintColumnName + ‘)

    GO’
    FROM
    @table

    GO

    Like

  2. In your case, the positive point point is that you do not need such a complex code as Sql Server 2000 does not support Schemas at all.

    Like

  3. need to get by db table names , is there any sys table where it ties up table with db_id in any of the system databases.

    Like

  4. Pingback: SQL SERVER – Download Microsoft SQL Server Compact 4.0 SP1 « SQL Server Journey with SQL Authority

  5. Hii All,

    I am using SQL CE…

    I have added two columns in my table Accounts (ID(PK), UpdateId , InsertId) and i have set default constraints for both with name Accounts_UpdateId_Default & Accounts_InsertId_Default but when i am using below query it showing only one constraint which system has created for ID(PK), It not showing those constraints which i have created.

    SELECT *
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE (table_name = ‘Accounts’)

    Please help me ASAP….. Thnx…

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s