How to get some specific information about columns in SQL Server CE

Question

I'm making an application in VB.NET to create and manage SQL Server CE Databases, and I have to get some information about the columns, to put them into a DataGridView (each row of the dgv refers to a column of a table of a database).

I made an incomplete query with some stuff found in the internet to get:

• Column Name;
• Type of data;
• If the column is a Primary Key;
• Default value of the column;
• If the column is nullable.

Here's the query:

SELECT COL.[COLUMN_NAME] AS COLNAME,
       COL.[DATA_TYPE],
       CASE WHEN IND.[COLUMN_NAME] IS NOT NULL THEN 1 ELSE 0 END AS PK,
       COL.[COLUMN_DEFAULT],
       CASE WHEN COL.[IS_NULLABLE] = 'YES' THEN 1 ELSE 0 END AS ALLOW_NULL
FROM INFORMATION_SCHEMA.COLUMNS AS COL LEFT JOIN INFORMATION_SCHEMA.INDEXES AS IND ON COL.COLUMN_NAME=IND.COLUMN_NAME AND COL.TABLE_NAME=IND.TABLE_NAME
WHERE COL.TABLE_NAME='MyTable'

It works quite well, but i also need the following info:

• If the column has identity; (solved, thanks to ErikEJ)
• If it allows duplicates. (partially solved)

How can i update the query to get those information in addition to what it gives me already?

Thanks and sorry for my awful English (I'm Italian and it's quite difficult for me to write in another language).

Answer 1

You can check if the autoinc_increment column IS NOT NULL, then it is an IDENTITY column. But yoy could also simply use my SqlCeScripting library to get all this information via code rather than having to re-invent these queries yourself - http://exportsqlce.codeplex.com