I thought it was a simple task but it's a couple of hours I'm still struggling :-(

I want to have the list of column names of a table, together with its datatype and the value contained in the columns, but have no idea how to bind the table itself to get the current value:

DECLARE @TTab TABLE
(
    fieldName     nvarchar(128),
    dataType      nvarchar(64),
    currentValue  nvarchar(128)
)

INSERT INTO @TTab (fieldName,dataType)
    SELECT
        i.COLUMN_NAME,
        i.DATA_TYPE
    FROM 
        INFORMATION_SCHEMA.COLUMNS i
    WHERE 
        i.TABLE_NAME = 'Users' 

Expected result:

 ------------ ---------- --------------- 
| fieldName  | dataType | currentValue  |
 ------------ ---------- --------------- 
| userName   | nvarchar | John          |
| active     | bit      | true          |
| age        | int      | 43            |
| balance    | money    | 25.20         |
 ------------ ---------- --------------- 

CodePudding user response：

In general the answer is: No, this is impossible. But there is a hack using text-based containers like XML or JSON (v2016 ):

--Let's create a test table with some rows

CREATE TABLE dbo.TestGetMetaData(ID INT IDENTITY,PreName VARCHAR(100),LastName NVARCHAR(MAX),DOB DATE);
INSERT INTO dbo.TestGetMetaData(PreName,LastName,DOB) VALUES
 ('Tim','Smith','20000101')
,('Tom','Blake','20000202')
,('Kim','Black','20000303')
GO

--Here's the query

SELECT C.colName
      ,C.colValue
      ,D.*
FROM 
(
    SELECT t.* FROM dbo.TestGetMetaData t
    WHERE t.Id=2
    FOR XML PATH(''),TYPE
) A(rowSet)
CROSS APPLY A.rowSet.nodes('*') B(col)
CROSS APPLY(VALUES(B.col.value('local-name(.)','nvarchar(500)')
                  ,B.col.value('text()[1]',   'nvarchar(max)'))) C(colName,colValue)
LEFT JOIN INFORMATION_SCHEMA.COLUMNS D ON    D.TABLE_SCHEMA='dbo'
                                        AND  D.TABLE_NAME='TestGetMetaData'
                                        AND  D.COLUMN_NAME=C.colName;

GO

--Clean-Up (carefull with real data)

DROP TABLE dbo.TestGetMetaData;
GO

Part of the result

 ---------- ------------ ----------- -------------------------- ------------- 
| colName  | colValue   | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | IS_NULLABLE |
 ---------- ------------ ----------- -------------------------- ------------- 
| ID       | 2          | int       | NULL                     | NO          |
 ---------- ------------ ----------- -------------------------- ------------- 
| PreName  | Tom        | varchar   | 100                      | YES         |
 ---------- ------------ ----------- -------------------------- ------------- 
| LastName | Blake      | nvarchar  | -1                       | YES         |
 ---------- ------------ ----------- -------------------------- ------------- 
| DOB      | 2000-02-02 | date      | NULL                     | YES         |
 ---------- ------------ ----------- -------------------------- ------------- 

The idea in short:

• Using FOR XML PATH(''),TYPE will create a XML representing your SELECT's result set.
• The big advantage with this: The XML's element will carry the column's name.
• We can use a CROSS APPLY to geht the column's name and value
• Now we can JOIN the metadata from INFORMATION_SCHEMA.COLUMNS.

One hint: All values will be of type nvarchar(max) actually. The value being a string type might lead to unexpected results due to implicit conversions or might lead into troubles with BLOBs.

UPDATE

The following query wouldn't even need to specify the table's name in the JOIN:

SELECT C.colName
      ,C.colValue
      ,D.DATA_TYPE,D.CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE
FROM 
(
        SELECT * FROM dbo.TestGetMetaData 
        WHERE Id=2
        FOR XML AUTO,TYPE
) A(rowSet)
CROSS APPLY A.rowSet.nodes('/*/@*') B(attr)
CROSS APPLY(VALUES(A.rowSet.value('local-name(/*[1])','nvarchar(500)')
                  ,B.attr.value('local-name(.)','nvarchar(500)')
                  ,B.attr.value('.',   'nvarchar(max)'))) C(tblName,colName,colValue)
LEFT JOIN INFORMATION_SCHEMA.COLUMNS D ON    CONCAT(D.TABLE_SCHEMA,'.',D.TABLE_NAME)=C.tblName
                                        AND  D.COLUMN_NAME=C.colName;

Why?
Using FOR XML AUTO will use attribute centered XML. The elements name will be the tables name, while the values rest within attributes.

UPDATE 2

Fully generic function:

CREATE FUNCTION dbo.GetRowWithMetaData(@input XML)
RETURNS TABLE
AS
RETURN
SELECT C.colName
      ,C.colValue
      ,D.*
FROM @input.nodes('/*/@*') B(attr)
CROSS APPLY(VALUES(@input.value('local-name(/*[1])','nvarchar(500)')
                  ,B.attr.value('local-name(.)','nvarchar(500)')
                  ,B.attr.value('.',   'nvarchar(max)'))) C(tblName,colName,colValue)
LEFT JOIN INFORMATION_SCHEMA.COLUMNS D ON    CONCAT(D.TABLE_SCHEMA,'.',D.TABLE_NAME)=C.tblName
                                        AND  D.COLUMN_NAME=C.colName;

--You call it like this (see the extra paranthesis!)

SELECT * FROM dbo.GetRowWithMetaData((SELECT * FROM dbo.TestGetMetaData WHERE ID=2 FOR XML AUTO));

As you see, the function does not even has to know anything in advance...