1
  • I have an existing table called Users, in a SQL Server Database
  • I wish to be able to generate a table that shows all the Column Headings in the Column 1, and 1 row of sample data in Column 2. Perhaps the Last row?
  • I need the query to be dynamic, so I can change the table name and capture all COLUMN_NAMES without having to hard-code them
Table: Users
UserID User_Name Country Employed
1 E24141 Elon Mosk US Y
2 E24142 Bill Jates US N
3 E24142 Jeff Lezos US N
Query1: Get List of Columns
COLUMN_NAME SAMPLE
UserID E24142
User_Name Jeff Lezos
Country US
Employed N

So far I have figured out how to create column 1, to get a list of Column Names as Rows.

SELECT COLUMN_NAME 
FROM ENT_Layer.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Users' AND COLUMN_NAME LIKE '%'

However, I can't find a way to add COLUMN 2, which is the 'Sample data'. I have read about PIVOT & UNPIVOT but I'm not sure that is what I need to do. I suspect I need to dynamically be able to select a names list of Column Names, which i can achieve with the code below, but am unsure of the next step.

Any advice?

DECLARE @Columns as VARCHAR(MAX)

SELECT @Columns =
    COALESCE(@Columns + ', ','') + QUOTENAME(COLUMN_NAME)
FROM (
    SELECT COLUMN_NAME
    FROM ENT_Layer.INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Users' AND COLUMN_NAME LIKE '%'
) AS B

Print @Columns
Dale K
  • 25,246
  • 15
  • 42
  • 71
Calico
  • 426
  • 1
  • 4
  • 17

1 Answers1

2

On second read, if you are looking for just ONE SAMPLE row

Select B.* 
 From  (Select top 1 * from AnyTableOrQuery Order by SomeCol Desc) A
 Cross Apply  (
                 Select [Key]
                       ,Value
                 From OpenJson(  (Select A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES )  ) 
              ) B

The INCLUDE_NULL_VALUES is optional

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • Thanks for the reply John. I have tried integrating your code but get the following error: Invalid object name 'OpenJson'. I am using SQL Server Management Studio (2016). Turns out my compatibility level is 110! So I might not be able to use JSON? – Calico Jan 13 '23 at 21:46
  • @Calico Select @@version This will give you an actual version. If <2016 there is an XML approach as well – John Cappelletti Jan 13 '23 at 21:49
  • Thanks. The version returns: 'Microsoft SQL Server 2017 (RTM-CU29) (KB5010786) - 14.0.3436.1 (X64) Mar 18 2022 13:21:03 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)'. From what i was reading online, compatibility versions < 130 don't allow the use of JSON – Calico Jan 13 '23 at 21:59
  • @Calico You should be good to go https://dbfiddle.uk/fnFfUF_S – John Cappelletti Jan 13 '23 at 22:01
  • I had to run a statement ALTER DATABASE DBNAME SET COMPATIBILITY_LEVEL = 130. After that the code worked great! However, ROW 1 is a default row (with default data). Is there a means to pick the last row? – Calico Jan 13 '23 at 22:10
  • 1
    @Calico In this example, I used Order by ID Desc https://dbfiddle.uk/QjACpfM1 – John Cappelletti Jan 13 '23 at 22:13