Fetching column names from another table in SQL

Question

I have two tables, one holding the actual data and another with human readable names for the columns.

The table holding the data is queried as follows.

SELECT 
       [H08CEC38C-281B-4C28-90DE-EEEC1D3C4D2E]
      ,[H8319D956-2223-41DC-AE91-B504832B8665]
      ,[H05E82ED3-517B-4545-A44E-1BDC2126A3AD]
      ,[H519574FD-EFC4-4BFB-8BC9-447FF61E0C0E]
FROM [Database].[dbo].[DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA]

In addition I have a 'meta' table with the actual names of the columns and the table itself.

Name                                    DisplayName Table                                       DisplayTable
[H08CEC38C-281B-4C28-90DE-EEEC1D3C4D2E] [ID]        [DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA]   [Datatable]
[H8319D956-2223-41DC-AE91-B504832B8665] [Name]      [DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA]   [Datatable]
[H05E82ED3-517B-4545-A44E-1BDC2126A3AD] [City]      [DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA]   [Datatable]
[H519574FD-EFC4-4BFB-8BC9-447FF61E0C0E] [Country]   [DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA]   [Datatable]

I'm looking for a way to replace what I suspect to be unique identifiers with the human readable names from the other table. I have multiple of these tables with a total of over 200 columns which are constantly expanding in length and width (with columns being added).

I suggest you to create a script based on this table to generate view for all tables, so you will have an easy access. You can also create insert and update triggers in the view to insert direct by the view. — Rogério Carvalho Batista, Jan 16 '18 at 13:08
Using inner join was my first instinct but I couldn't figure out how to inner join the column names itself instead of the data in the columns. `inner join metatable meta on meta.name=datatable.?` — Stephan Uchida, Jan 16 '18 at 13:23
Possible duplicate of [How to join (merge) data frames (inner, outer, left, right)?](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) — Rome_Leader, Jan 16 '18 at 13:28
If this is for fiddling about in the database, you can generate a query (or create a `view`) using dynamic SQL. (See [`sp_executesql`](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql).) Otherwise it may make more sense to leave it to the application(s) to sort things out, e.g. by using a stored procedure that returns two recordsets, the first containing the "magic decoder ring" and the second containing the data. — HABO, Jan 16 '18 at 13:53

score 0 · Answer 1 · answered Jan 16 '18 at 14:59

I suggest to create a copy of the tables with a select into which maps the column names to their display names. The only challenge is to create a series of 200 select into and execute them. A view would work the same way, but id you want to create indexes it will be a challenge. All depends on the size of the tables and how frequently they have to be refreshed.

You can create the series with a SELECT against a combination of your meta table and the sys.tables, sys.columns and sys.schemas, using quotes and + to concatenate the query into a single line that does the SELECT INTO. You output this into a memory table called @Query. Then you loop through @Query and execute one by one each line in dynamic SQL.

Below is the join of sys.tables, sys.columns and sys.schemas which you should join against your meta table (this will help validating the column names to spot mistakes)

SELECT 
s.name AS schema_name,
t.name AS table_name, 
c.name AS column_name
    FROM sys.tables t JOIN sys.columns c ON c.object_id = t.object_id JOIN sys.schemas s ON t.schema_id = s.schema_id

HABO · Answer 2 · 2018-01-16T17:14:27.050

The following code demonstrates one way of swapping column names. Rather than generating a select query it could be modified to generate a statement to create a stored procedure or view.

-- Sample data.
create table Metadata ( MetadataId Int Identity,
  TableName VarChar(16), ColumnName VarChar(16), DisplayName VarChar(16) );
insert into Metadata ( TableName, ColumnName, DisplayName ) values
  ( 'Samples', '001', 'ShoeSize' ), ( 'Samples', '042', 'TowelLocation' ),
  ( 'Widgets', 'BR549', 'RingTwo' );
select * from Metadata;

create table Samples ( SampleId Int Identity,
  [001] VarChar(5), [042] VarChar(64) );
insert into Samples ( [001], [042] ) values
  ( '5EEE', 'Left shoulder.' ), ( '14', 'Front hall closet.' );
select * from Samples;

-- Build a query.
declare @TableName as sysname = 'Samples';
declare @ColumnList as NVarChar(2048) = N'';
select @ColumnList = Stuff(
    ( select N',' + QuoteName( M.DisplayName ) + N'=' + QuoteName( M.ColumnName )  
     from sys.columns as C inner join
    Metadata as M on M.TableName = @TableName and M.ColumnName = C.name
  where object_id = Object_Id( @TableName )
  order by column_id for XML path(''), type).value('.[1]', 'VarChar(max)' ),
    1, 1, '' );
declare @Query as NVarChar(4000) = N'select ' + @ColumnList + N' from ' + QuoteName( @TableName );

-- Try it on for size.
select @Query as Query;
execute sp_executesql @statement = @Query;

-- Houseclean.
drop table Samples;
drop table Metadata;

score 0 · Answer 3 · answered Jan 17 '18 at 05:38

Now as far as i have understood your issue, You need to replace Name of the Table and Column from Unique Identifier form to Human Readable form based on Data available in Meta table.

Now, First create a table whose column and table name needs to be changed

--Table whose column name and Table name needs to be changed
CREATE TABLE [DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA]
(
[H08CEC38C-281B-4C28-90DE-EEEC1D3C4D2E] VARCHAR(100),
[H8319D956-2223-41DC-AE91-B504832B8665] VARCHAR(100),
[H05E82ED3-517B-4545-A44E-1BDC2126A3AD] VARCHAR(100),
[H519574FD-EFC4-4BFB-8BC9-447FF61E0C0E]  VARCHAR(100)
)

--Meta Table 

CREATE TABLE Meta
(
Name VARCHAR(100),
DisplayName VARCHAR(100),
[Table] VARCHAR(100),
DisplayTable VARCHAR(100)
)


INSERT INTO Meta VALUES ('[H08CEC38C-281B-4C28-90DE-EEEC1D3C4D2E]','[ID]','[DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA]','[Datatable]')
INSERT INTO Meta VALUES ('[H8319D956-2223-41DC-AE91-B504832B8665]','[Name]','[DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA]','[Datatable]')
INSERT INTO Meta VALUES ('[H05E82ED3-517B-4545-A44E-1BDC2126A3AD]','[City]','[DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA]','[Datatable]')
INSERT INTO Meta VALUES ('[H519574FD-EFC4-4BFB-8BC9-447FF61E0C0E]','[Country]','[DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA]','[Datatable]')

Now changing the column name in SQL Server is done using sp_rename procedure, So we will need to create a script that will on Meta table and will provide you output in sp_rename format. Following example will make it clear

--Required Script
SELECT *,'EXEC sp_rename '''+ [Table] + '.' + Name + '''' + ',''' + DisplayName + '''' + ',' + '''COLUMN' + '''' AS Rename_Script FROM Meta

Output of this script will include a column "Rename_Script", which on running will alter the name of the column in [DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA] as mentioned by you in example.

EXEC sp_rename '[DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA].[H08CEC38C-281B-4C28-90DE-EEEC1D3C4D2E]','[ID]','COLUMN'
EXEC sp_rename '[DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA].[H8319D956-2223-41DC-AE91-B504832B8665]','[Name]','COLUMN'
EXEC sp_rename '[DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA].[H05E82ED3-517B-4545-A44E-1BDC2126A3AD]','[City]','COLUMN'
EXEC sp_rename '[DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA].[H519574FD-EFC4-4BFB-8BC9-447FF61E0C0E]','[Country]','COLUMN'

Similarly, You need to create a script for changing Table name. Please let me know in case you face any issues with the same.

score 0 · Answer 4 · answered Jan 17 '18 at 19:13

    IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
    CREATE TABLE #t(Name VARCHAR(255),DisplayName VARCHAR(255),[Table] VARCHAR(255),DisplayTable VARCHAR(255))
    INSERT INTO #t(Name,DisplayName,[Table],DisplayTable) 
    SELECT '[H08CEC38C-281B-4C28-90DE-EEEC1D3C4D2E]','[ID]','[DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA]','[Datatable]' UNION ALL 
    SELECT '[H8319D956-2223-41DC-AE91-B504832B8665]','[Name]','[DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA]','[Datatable]' UNION ALL 
    SELECT '[H05E82ED3-517B-4545-A44E-1BDC2126A3AD]','[City]','[DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA]','[Datatable]' UNION ALL 
    SELECT '[H519574FD-EFC4-4BFB-8BC9-447FF61E0C0E]','[Country]','[DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA]','[Datatable]'

    DECLARE @sql VARCHAR(max),@tablename VARCHAR(255)
    SET @tablename='[DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA]'
    SELECT @sql=ISNULL(@sql+',','')+'t.'+t.Name+' AS '+t.DisplayName
    FROM #t AS t WHERE t.[Table]=@tablename
    SET @sql='SELECT '+CHAR(13)+@sql +CHAR(13)+'FROM '+@tablename
    PRINT @sql
    'EXEC(@sql)

SELECT 
t.[H08CEC38C-281B-4C28-90DE-EEEC1D3C4D2E] AS [ID],t.[H8319D956-2223-41DC-AE91-B504832B8665] AS [Name],t.[H05E82ED3-517B-4545-A44E-1BDC2126A3AD] AS [City],t.[H519574FD-EFC4-4BFB-8BC9-447FF61E0C0E] AS [Country]
FROM [DIM4DE53E67-85C8-4274-B5ED-5F3526314DEA]

Fetching column names from another table in SQL

4 Answers4