Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
--This SQL block loops through all the columns in a table and sets the that data in the first record equal to the column name
--If your table is empty, you need to insert one record first
--All of your columns need to be set a to a datatype that will except the column name (varchar, nvarchar, etc)
DECLARE @loopcount int, @column int
SET @loopcount = 1
SET @column = (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_catalog = 'DatabaseName' --replace DatabaseName with your database name
AND table_name = 'TableName' --replace TableName with your table name
)
WHILE @loopcount <= @column
BEGIN
DECLARE @query nvarchar(max), @cursor_col nchar(50), @data nvarchar(max)
SET @cursor_col = (SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'DatabaseName' --replace DatabaseName with your database name
AND TABLE_NAME = 'TableName' --replace TableName with your table name
AND ORDINAL_POSITION = @loopcount
)
SET @data = ''''+@cursor_col+''''
SET @query =
'UPDATE TableName
SET '+convert(varchar,@cursor_col)+' = '+@data+'
WHERE PrimaryKey = 1' --set PrimaryKey as the Column holding the primary key and change 1 to the record you want to hold the column names
SET @loopcount = @loopcount + 1
EXEC(@query)
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment