CODEWITHSUNDEEP
sql-server
Stephen Pefanis
Stephen Pefanis

Reputation: 365

SQL Server unique column ID's

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

Upvotes: 0

Views: 395

Answers (3)

JBrooks
JBrooks

Reputation: 10013

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

Upvotes: 1

Hila DG
Hila DG

Reputation: 718

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

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

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

Upvotes: 0

Sufyan Jabr
Sufyan Jabr

Reputation: 809

You can try this... its a composite of two queries:

This will get you the list of coumns inside a table:

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) = 'TableName'

You can generate a HashCode of the column name and combine it with object_id... which should be unique unless the column name changed

Hope this will help.

Upvotes: 0

Related Questions