sql server table column list with comma as seperator

Below function mostly will be used for automated tasks in SQL Server to produce the insert statements for data population and migration from other systems.


make sure to use the schema name with the table name eg: dbo.customers or sales.customer.
select dbo.fnColList 'dbo.customers'

-- MUST Pass the schema name before the table --select SELECT DBO.fnColList('DBO.myTable') Create function DBO.fnColList(@in_vcTbl_name varchar(8000)) returns varchar(8000) as begin declare @colList2BuildAuditTable varchar(max) SELECT @colList2BuildAuditTable = coalesce(@colList2BuildAuditTable+ ',', '')+ '['+ B.NAME +']' FROM SYS.TABLES A JOIN SYSCOLUMNS B ON A.OBJECT_ID = B.ID WHERE SCHEMA_NAME(A.SCHEMA_ID) +'.'+A.NAME= @in_vcTbl_name ORDER BY B.COLORDER return @colList2BuildAuditTable end