Alternatives · Basic Logics · Real Scenario Logic's

Listing Comma Separated Columns instead of ‘*’ in SQL Server

Introduction

In any scenarios, if we need to explicitly list out all the column names of a table instead of using ‘*’, we can use like below Script-

Sample SQL Script

IF(OBJECT_ID('TestEmp') IS NOT NULL)
DROP TABLE [TestEmp];

CREATE TABLE dbo.[TestEmp]
(
	TestEmp_Key INT IDENTITY(1,1) NOT NULL,
	EmpName VARCHAR(100) NOT NULL,
	Age INT NULL,
	[Address] VARCHAR(100) NULL,
	Inserted_dte DATETIME NOT NULL
 CONSTRAINT [PK_TestEmp] PRIMARY KEY CLUSTERED 
 (
	TestEmp_Key ASC
 )
);
GO

INSERT INTO dbo.[TestEmp] (EmpName,Age,[Address],Inserted_dte)
VALUES ('Arul',24,'xxxyyy',GETDATE()),('Kiran',22,'zzzyyy',GETDATE());
GO

--Using '*'
SELECT * FROM [TestEmp];
GO

Sample Image

Listing Comma Separated Columns in Vertical manner for SELECT Statement

SQL Script

--Using Column Names with ',' instead of '*' (Vertical)
SELECT CONCAT(NAME,',') AS COLUMN_NAMES FROM SYS.COLUMNS WHERE [OBJECT_ID]=OBJECT_ID('TestEmp');
GO

Image

Listing Comma Separated Columns in Horizontal manner for SELECT Statement

SQL Script

--Using Column Names with ',' instead of '*' (Horizontal)
SELECT STRING_AGG(NAME,',') AS COLUMN_NAMES FROM SYS.COLUMNS WHERE [OBJECT_ID]=OBJECT_ID('TestEmp');
GO

Image

In the above script, we have used STRING_AGG() Function that works well for SQL SERVER 2017 and later versions.

For Pre-SQL SERVER 2017 Versions, we can use like below-

SELECT 
STUFF( (SELECT ',' + CAST(t.NAME AS VARCHAR(MAX))
        FROM SYS.COLUMNS t
		WHERE t.[OBJECT_ID]=OBJECT_ID('TestEmp')
        FOR XML PATH ('')
        ), 1, 1, ''
    ) AS COLUMN_NAMES;
GO

Note-

We have used a system table named ‘sys.columns‘ in above scripts. For every column added in a database, a record is created in the sys.columns table. We can also make use of a system view named ‘INFORMATION_SCHEMA.COLUMNS‘ where the Column_Name and Table_Name fields are used to fetch and filter things as we want.

Conclusion

Thus, we saw how can we List out Comma Separated Columns of a table in both Horizontal and Vertical manner for SELECT Statement in SQL Server. This Scripts are unit tested in both Azure SQL DB and On-Premises.

Thanks for reading!

FOLLOW BLOG for interesting upcoming posts!

Advertisement

One thought on “Listing Comma Separated Columns instead of ‘*’ in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s