My database has many columns that are reused across tables, but the name of the column changes.
edit: as an example: We're in telecom and deal with the 10 digit telephone number. It's used across 100s of tables, usually with a different column_name for each table, and the datatype and format of the column are not consistent (e.g. '123-456-7890', '1234567890', int 1234567890, 1800-123-456-7890).
For training and analysis, I want to be able to link or identify these duplicates with different names.
How can I do this?
example: I work in telecom and we use the telephone number in many different ways. That data point (the telephone number) is reused in literally 100s of tables, and it has dozens of different column names
Ok, no, SQL Server can't identify these columns automatically for you.
If you can come up with rules for programmatically identifying a telephone number column based on available metadata (in sys.columns, for instance) you can write a query that will get all such columns.
If you have to go through your columns and manually decide which ones are telephone number columns, you can start manually storing this information in a meta-table of your own design. Something like:
TableName ColumnName StandardName
Table1 Phone PhoneNumber
Table2 HomePhone PhoneNumber
Table2 WorkPhone PhoneNumber
Where TableName and ColumnName are the actual names, and StandardName is a value you manually assign that will be the same "Standard" name for all the "Identical" columns.
And then this meta-table could be used/queried for reference, training and analysis.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments