Advertisements
Let’s say I have some table Table1
:
Column1 | Column2 | Column3 | Column4 | … | … |
---|---|---|---|---|---|
values | values | values | values | … | … |
Is there a way to insert the column names into a temporary table
#Temp:
ColumnNames |
---|
Column1 |
Column2 |
Column3 |
Column4 |
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.
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
COLUMN_NAME |
---|
ID |
city |
State |
Population |
InsertedOn |