Columns to Rows with unknown number of columns (SQL Server & SMSS 18)

Let’s say I have some table Table1:

Is there a way to insert the column names into a temporary table

#Temp:

I want a generic function or method to do this for a variable number of columns (I want to apply this method to multiple tables with a different number of columns).

I have looked at forums describing pivot and unpivot, but they either hardcode their column names or are only used for a specified number of columns. I am very new to SQL.

>Solution :

Check it out.

The INFORMATION_SCHEMA.COLUMNS view has many other useful columns.

INFORMATION_SCHEMA.COLUMNS

SQL

-- DDL and sample data population, start
USE tempdb;
GO

DROP TABLE IF EXISTS dbo.tbl;

CREATE TABLE tbl  (ID INT IDENTITY PRIMARY KEY, city VARCHAR(20), State CHAR(2), Population INT, InsertedOn DATETIMEOFFSET(3) DEFAULT SYSDATETIMEOFFSET());
INSERT tbl (city, State, Population, InsertedOn) VALUES
('Miami', 'FL', 350000, Default),
('Orlando', 'FL', 300000, Default),
('Dallas', 'TX', 5000000, Default);
-- DDL and sample data population, end

DECLARE @tbl_catalog sysname = 'tempdb'
    , @tbl_name sysname = 'tbl'
    , @schema_name sysname = 'dbo';

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = @tbl_catalog
    AND TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = @tbl_name;

Output