Nov 11, 2013

How to get the all the columns name of a given table delimited by comma in sql server?

In sql server information about all the columns and its properties of a table is stored in the system view sys.Columns.

Suppose we have created a table in sql server by following query:

CREATE TABLE Student(
    Roll_No BIGINT IDENTITY,
    Stu_Name VARCHAR(100),
    DOB DATETIME,
    IsActive BIT
)

Sql query to get the all the columns name delimited by comma of Student table

DECLARE @ColumNames VARCHAR(1000)
SET @ColumNames = ''

SELECT @ColumNames = @ColumNames + name + ','
FROM Sys.columns
WHERE object_id = OBJECT_ID('Student')

SELECT LEFT(@ColumNames,LEN(@ColumNames) - 1) AS CSV

Output:
CSV
Roll_No,Stu_Name,DOB,IsActive


Sql query to get the all the columns name delimited by Tab of Student table

DECLARE @ColumNames VARCHAR(1000)
SET @ColumNames = ''

SELECT @ColumNames = @ColumNames + name + CHAR(9)
FROM Sys.columns
WHERE object_id = OBJECT_ID('Student')

SELECT @ColumNames AS TabDelimited

Output:
TabDelimited
Roll_No Stu_Name DOB IsActive

No comments:

Post a Comment