Cách lấy danh sách cột của bảng

[TrungBravo]

DECLARE @_ColumnList nvarchar(2000)
-- Cách 1: dùng SELECT với FOR XML PATH('')
SELECT @_ColumnList = STUFF((SELECT ',' + Name FROM Sys.Columns WHERE Object_Id = Object_Id('B20DmTk')
FOR XML PATH('')), 1, 1, N'')

PRINT @_ColumnList

-- Cách 2: dùng biến
SET @_ColumnList = ''
SELECT @_ColumnList = @_ColumnList + ',' + Name FROM Sys.Columns WHERE Object_Id = Object_Id('B20DmTk')

PRINT STUFF(@_ColumnList, 1, 1, N'')

-- Cách 3: làm thế nào bây giờ?

[BravoFC]

--Cách 3

DECLARE @_ColumnList nvarchar(2000)
SET @_ColumnList = ''
SELECT @_ColumnList = @_ColumnList + ',' + Column_Name FROM Information_Schema.Columns
WHERE Table_Name='B20DMTK'

PRINT STUFF(@_ColumnList, 1, 1, N'')


--Cách 4 nông dân

DECLARE @_Table TABLE (NAME VARCHAR(10))
DECLARE @_Name VARCHAR(10), @_List VARCHAR(4000)
INSERT INTO @_Table
SELECT NAME FROM Sys.Columns WHERE Object_Id = Object_Id('B20DmTk')

SET @_List =''
WHILE (SELECT COUNT(*) FROM @_Table) > 0
BEGIN
SELECT TOP 1 @_Name = NAME FROM @_Table
SET @_List = CASE WHEN @_List ='' THEN @_Name ELSE @_List + ',' + CAST(@_Name AS VARCHAR(10)) END
DELETE FROM @_Table WHERE NAME = @_Name
END
SELECT @_List