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://blog.sqlauthority.com)
6 Comments. Leave new
–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
SELECT * FROM INFORMATION_SCHEMA.INDEXES
DOESN’T WORK IN SQL SERVER 2000
Note that SELECT * FROM INFORMATION_SCHEMA.INDEXES will work only in SQL CE version only
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.
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.
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…