Good DayDoes SQL Server internally manage unique ids for every
column in every table of every database that we create? If it does, how
can we access those ids? I have gone through sys.columns but the
id of the column and even the table, does not remain the same. I mean
the moment we change the data type of the column, the id of the table is
re-generated [it seems]. Also if we delete some columns from the table
and add a new one, its id would be generated like identity. But again
when we change the data type, it is reset back sequentially.
not that I know of. (but Tables do - in the [object_id] column)
What I have seen done is to create your own [Named] Property on a column, and store, say, a GUID in that. Scripts which SQL generates when you use Table Designer to modify columns (e.g. insert a column which causes all the ones below to get a Sequence Number of +1 ) will preserve all such Properties. (SQL uses the Property thingie for descriptive names that you give to Tables, Columns and so on; not sure that SQL, itself, uses it for anything else though). The syntax for the Properties Table is ghastly (its a key-value type table with far too many key columns IMHO - which would all be solved if the Table, Column and all other objects did just have a unique GUID attached to them ... Ho!Hum!
can you please explain the reason for wanting to access this? what you trying to accomplish?
I want to use this to store a GUID for each column, becuase we have some Metadata about columns in the database, and if there is a change in the physical schema I need to synchronise our Metadata. If a column is renamed AND moved in the Column Order the way our update routine currently works is ... ermmm ... "naff" !!
So tying a GUID to the Column, and have it be preserved when the Schema changes, would be a big help.
I saw a really cool article about using the extended properties to store some data but I'm blowed if I can find it now. I think it was to do with automatically generating scripts for a rollout by being able to establish what had changed, or perhaps it was some sort of documentation capability (i.e. tied to the Schema objects). I thought I made a careful note of it as I was sure I would want it again ... that's the story of my life, of course !!
sounds very nauseating to maintain 
how about you create your own reference points to manage instead of relying on SQLs.
select t.name, c.name, st.name, st.name , c.user_type_id from sys.tables t join sys.columns c on t.object_id = c.object_id left join sys.types st on st.system_type_id = c.system_type_id
Well .. intention is to prevent, or at least reduce, the nauseum!
My Meta Data contains things like Minimum Length (to provide a user-friendly data validation message on-screen), data type (e.g. sub-sets of VARCHAR for All Caps, or a reduced character set which contains only letters and numbers, and so on). Then there is a "Form Legend" for each column, suitable for display on any forms (along with foreign language translations), plus what we call "Operator Lead Through" which provides hints on usage of the field. Similarly short / abbreviated headings suitable for report columns, and so on ... I have loads of other such stuff stored in my own tables, comprising "meta data", in our APP databases.
All CRUD routines (forms too but also SProcs for Get / Save / Delete etc), by default, are automatically generated from that information (in real time, not by generating some static STRUCT and having to re-build that whenever the database structure changes). I would say 90% of our CRUD routines have no additional code (i.e. the meta data is extensive and avoids, in most cases, the need for any custom code; if we have a new Use Case that seems to be occurring frequently we make it a standard-case instead).
Bottom line: I want the computer to write the software, its far more reliable than I am! What I don't want is any DDL changes to the DB Schema de-Syncing my own Meta Data.
Actually, what I want is that I can change the Meta Data and have that DRIVE the DDL script to change the database to match. I know all such things should be carefully designed by committee and using Due Process and all that ... sorry chaps ... but for much of our boring work it is much easier to just say "Make that bigger" or "Change that from VARCHAR to INT now that we realise that the client meant to store a number" and just press on. Our Front End [i.e. Meta Data maintenance] tools let us change all the characteristics of a table / column and then all the Forms, SProcs for CRUD and so on "just happen", in real time, without having to do a BUILD of the whole work, This is a process I have used, and refined, for decades and I find it easy and cheap (for client). And fewer bugs.
But that's just me ... 
Downside is that I do need to SYNC my Meta Data with SQL's - if a Column Name changes my Sprocs need to change to use the new column name, of course, so I need a means of knowing what the Old and New names are - and my GUID extended property, on a column (and a table) achieves that.
wow, very impressive (putting away my barf bag 
) !! So have you looked at leveraging server/database/table level triggers to spawn or seed X to be used by the process. maybe Service Broker? deep stuff
I have a similar meta-data setup (a pure hierarchy, the top level of which is the company itself).
You must assign your own id to provide a unique value for each "element" you want to track -- I use a bigint for that, to make sure I never run out of numbers -- such as tables, indexes, or columns.
It's actually easy enough to update the data type when a given column name changes data type.
The really hard part is with renaming of elements. That's a constant pain.
I use extended properties to store metadata.
In SQL Server many extended properties can be stored for each object. Each extended property has a name, and that name must be unique for each object. If I have a Cases table with a CaseDescription column I can have only one Length property on the column, but I can add as many distinctly named properties for a given object as I want.
USE AdventureWorks2008R2;
GO
EXEC sys.sp_addextendedproperty
@name = N'MS_DescriptionExample',
@value = N'Minimum inventory quantity.',
@level0type = N'SCHEMA', @level0name = Production,
@level1type = N'TABLE', @level1name = Product,
@level2type = N'COLUMN', @level2name = SafetyStockLevel;
GO
SELECT * FROM sys.extended_properties ;I was OK with that, provided that the column didn't also change Ordinal Position ... and given I was in charge of Schema changes I would do "Rename" and "Move position" as two separate updates, to avoid that issue. Bah! That was a pain! so having a unique, non-changing, ID for each object is a much better solution IMHO.
We use GUID (rather than INT) so that it is unique across DBs. We often want to merge etc. DB Schema changes from a Client Project into either another-client-project or our master-deployment-killer-APP database 
I'm at a loss as to why MS has not introduced an object_id lookalike column in sys.columns, can it really hurt that much to have a redundant column? Maybe the number of Wanters, like us, is actually tiny ...
I never use guids unless absolutely unavoidable.
The really important numbers -- company itself, company sites, servers, etc. -- have a separate pre-assigned range of numbers and are generally assigned by hand.
The other numbers are assigned by each system. Each system is given an initial, unique pool of numbers to assign for elements within that system (somewhat like DNS network id assignments work). When a system runs out of available numbers, it requests a new pool of numbers from a central assignor. If that assignor is not available, it has an emergency pool of numbers pre-assigned that it can use.
I don't follow. You certainly can't count on the same ordinal position for column changes, and ordinal position isn't really even relevant for things like indexes.
Yes, having a unique, non-changing ID is the whole point, but how do you change the name for that id when the name changes in real life? That's by far the biggest problem point I've had. But sometimes things just have to be renamed.
Why store metadata already readily available in the system views like length, data type, ordinal position, etc. Using extended properties and the system views is a much simpler and easier to maintain solution.
For table columns, the object_id and column_id together uniquely identify columns. Stop reinventing the wheel.
We have that, but we found that it was fragile as the number of granted systems grew (every clients' projects' DB in our case). If we had had a web service that could allocate them in real time (I didn't think of your emergency pool, but that's a good idea) that would work, but at the time (late 90's) a service like would have been difficult to build, and we thought that the number of allocated number-ranges would be small, but as they grew we failed to decide to build such a "number allocation service". So we went to GUIDs instead ... (We do actually have INTs for all local use, within the DB itself, we just use the GUIDs for global reconciliation)
But I agree with your point "I never use guids unless absolutely unavoidable"
Indeed. Sorry, I wasn't clear ('coz its a long story!). If Ordinal Position and Name are the same then assume its the same column (there have been times where we have swapped two columns in some way where that might have been false, but its probably good for 5 x 9's, or something like that!)
If Ordinal Position is different, but column name still exists then assume its still the same column, but ask for confirmation. What happened in this case was that all columns AFTER the newly inserted/deleted column would have ordinal position +1 or -1, so it was easy to confirm that they were all, indeed, the same column.
If column name cannot be found assume it is renamed,. Request confirmation and offer new column name at same ordinal position as the default choice. (This is after the previous step, so any obvious Ordinal Position Changed but Column Name is the Same, has already happened). But, yeah, it was pants really !
Anyway, long story short, it was a nightmare and luckily I stumbled over the article, which I can no longer find :(, that suggested using extended properties for a persistent Global ID. I'd used extended property for the Descriptions in SSMS Table Designer for years, and seen them pop up in the DDL changes scripts that SSMS Table Designer generates, but never put 2-and-2 together that any extended properties that I created would also similarly survive.
Now I've got them I'm not looking back!
I'm not sorry if that was not clear.
My Meta Data is storing enhanced data types such as "Force Caps" or "restricted character set", "Minimum Length" / "Required" type attributes (i.e. to [dynamically] control data validation), and Legends to display on a Form (including Foreign variations) and "narrow" labels for column headings on reports and so on.
I only stored the Ordinal Position (and Name) to help with synchronising my meta data with the physical column's data, and that's the thing that is made much easier by, now, storing MY UniqueID for my Meta Data as an extended property of the column.
Sorry, missed your message first time around.
I never considered using extended properties for each of my properties, because I started off with them in my own table. I don't suppose Extended Properties existed back then - it would have been in SQL 6.5 !!
Personally, I'm happier with them in my own table as (I think) I have better performance [than having to query multiple attributes, individually, from the extended properties, or using some sort of PIVOT, as compared to getting all of them as a single row in my Column Meta Data table].
But it may well be as-broad-as-it-is-long ...
To: jotorre_riversidedpss.org
-
You need to think more broadly. This isn't just about tables and columns. As I noted, I go all the way from the company itself (element = 1 in my model) on down.
-
The idea is to have a stable identifier:
2A) to maintain metadata info for a given business element, such as a table column, which includes its data domain (not exactly data type, because data type may be tied to a specific dbms) and length, because they are critical to defining the data itself. [The ordinal position would never be stored in your "dictionary" data.]
and/or
2B) to link to documentation about that element
and/or
2C) to provide info about the source of this data
and/or
2D) to provide info about the use(s) of this data
and/or
2E) to correlate different elements of data to the same core piece of information. For example,customer_id might appear in many tables, under many different names, sometimes with different sizes or even data types. But if all those columns represent the same business information, it's nice to have some way to document that, and trace/link it.
Oh, btw, the wheel has been reinvented (thousands of times actually), otherwise we'd all have wooden wheels with no rims!
1 Like
I generate extended properties from the core dictionary. I have code to pull changes from extended props into the dictionary but I deeply discourage people (other than end users) from making changes there.
My documentation also has security/need-to-know coding for each element. For example, what an end user sees for the definition of customer_id is different from what the programmer sees which is different from what the DBA sees.
does all this extended properties eco system move with the db(s) if and when you migrate to a new server?
If you move the entire db, yes, the extended properties would go along.