SQL storing data about columns

Question

I'm learning SQL Server Compact for a program I'm writing that requries a local database. I have multiple tables, each with different columns and I'd like to mark each column as being a certain type (not a data type, just an integer tag) to let the program know what to do with it. I'm clueless about SQL. How does one do this?

Answer 1

SQL Server stores this information in system tables. Sysobjects hold table info. Syscolumns holds column info. You can find data typy in systypes.

Sysobjects and syscolumns join on the id column. I don't remember bow to join to systypes but a quick google search will give you the answer.

Answer 2

I would suggest you create User-Defined types and let your code work on the type you defined, ie.

EXEC sp_addtype telephone, 'varchar(24)', 'NOT NULL'

Now, your program would see the type Telephone and decide what to do

Answer 3

I would use extended properties to store that meta data. For example:

EXEC sp_addextendedproperty N'columntag', N'123', 'SCHEMA', N'dbo', 'TABLE', N'mytable', 'COLUMN', N'id'

SELECT value AS columntag
FROM fn_listextendedproperty('columntag', 'SCHEMA', 'dbo', 'TABLE', 'mytable', 'column', 'id')

Replace 'columntag' with whatever you want to refer to your "integer tag" as, 'mytable' with the name of your table, 'id' with the name of the column in question, and the '123' is your integer value you are storing for lookup. The first statement adds the extended property, and the second is how you would/could retrieve it programatically.