Question: I am working on an application that we will be using to maintain our database dictionary (description of each column in each table) and would like to build a "Refresh" function that can go to the database and get the updated column list/names.

What I'm having issues with is when a column is renamed/moved, I would like to track if there is way to update the database with the new details. I tried using the `Column_ID` from `syscolumns`, but this changes when the column is moved so seems to be an arbitrary number.

Is there any unique ID that SQL Server generates for a column that doesn't change?

My alternative is to add the new column and mark the old one as removed.

Thanks

Answer: 
Why don't you store the description in the column's description property. Then when the column is updated the developer would be responsible for updating this.

I wrote a web font-end to this over 10 years ago.... I could send you the code if you want? Looked like:

[![enter image description here](https://i.stack.imgur.com/310Qx.png)](https://i.stack.imgur.com/310Qx.png)
I would add a new column and mark the old one as removed. 

[edit] 

I thought that what SQL server deleted the table and create it in the first place, and after your question I went to investigate:

If you mark the option of "Prevent saving changes that require table re creation" (in Options -> Designers -> Table and database designers) and then create the following table:

```
CREATE TABLE [dbo].[testTbl](
    [colA] [nchar](10) NULL,
    [colB] [smallint] NOT NULL
) ON [PRIMARY]

```

and even insert some values:

```
INSERT INTO dbo.testTbl
VALUES ('text1', 12)
,('text2', 22)
,('text3', 32)
,('text4', 42)
,('text5', 52)

```

And then run the code from above you will get:

```
SELECT c.object_id, OBJECT_NAME(c.object_id) AS OBJECT_NAME
    ,c.NAME AS COLUMN_NAME
    ,t.NAME AS DATA_TYPE
    ,c.max_length AS MAX_LENGTH
FROM sys.all_columns c
INNER JOIN sys.types t ON t.system_type_id = c.system_type_id
WHERE object_name(c.object_id) = 'testTbl'

```

You will get the following values:
[![enter image description here](https://i.stack.imgur.com/dfwSj.jpg)](https://i.stack.imgur.com/dfwSj.jpg)

If you try to do now the following changes (in the table designer)you will find out that all require drop and re create from the database:  

Change a NULLABLE column to non-nullable  

change non-nullable column to a nullable  

(which is to be expected; those changes require the columns to move around the page)  

change the datatype of a column (nchar(10) to more or less, smallint to tinyint or int)  

but if you want to change the column from B to C, SQL can manage it, and the results would be: 

[![enter image description here](https://i.stack.imgur.com/gDvcM.jpg)](https://i.stack.imgur.com/gDvcM.jpg)

The object id refers to the table id, and therefore the table stayed with the same object id.