|
Execute the following T-SQL example script in Microsoft SQL Server Management Studio Query Editor to demonstrate the generation of the comma-limited column list - columns name - for a table.
-- SQL information_schema views
-- SQL information schema columns - mssql columns name
USE AdventureWorks
DECLARE @Schema VARCHAR(64),
@Table VARCHAR(64),
@Columns VARCHAR(2000)
SET @Table = 'Contact'
SET @Schema = 'Person'
-- SQL string append - string concatenation
-- SQL make comma-limited list - make csv list
SELECT @Columns = COALESCE(@Columns + ', ','') + column_name
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = @Table
AND table_schema = @Schema
ORDER BY ordinal_position
-- MSSQL columns list for a table
SELECT @Columns
GO
/*
ContactID, NameStyle, Title, FirstName, MiddleName, LastName, Suffix, EmailAddress,
EmailPromotion, Phone, PasswordHash, PasswordSalt, AdditionalContactInfo,
rowguid, ModifiedDate
*/
------------
|