Microsoft – SQL Server – Comma Delimited List (of column names)

I am reviewing a piece of code that builds column list and not really so sure how “heavy” it is.

Heavy in the sense that is it expensive, in terms of:

  • Accessing System table(s) to query for objects meta data
  • Being a scaler function

And, so I wanted to re-factor it out to its own function and dig in a bit deeper:

Goggled for help and found good, reliable code on building comma delimited list.

Here is my early Saturday morning take of it…







if OBJECT_ID('dbo.student') is not null
begin
	drop table dbo.student

end
go

create table dbo.student
(
    [id] bigint not null identity(1,1)
  , [firstname] sysname
  , [lastname] sysname  
)
go

if OBJECT_ID('dbo.courses') is not null
begin
	drop table dbo.courses

end
go

create table dbo.courseName
(
    [id] bigint not null identity(1,1)
  , [courseName] sysname
)
go

if OBJECT_ID('dbo.studentSchedule') is not null
begin
	drop table dbo.studentSchedule

end
go

create table dbo.studentSchedule
(
    [id] bigint not null identity(1,1)
  , [studentID] bigint
  , [courseID] bigint
)
go




if OBJECT_ID('dbo.fn_ListofColumns') is not null
begin
	drop function dbo.fn_ListofColumns

end
go


create function dbo.fn_ListofColumns
(
	@excludeIdentityColumn bit
)
returns @listColumn TABLE
(
	  id int not null identity(1,1)
	, schemaName sysname  	
	, objectName sysname
	, objectID int --as object_id(objectName)
	, columnList varchar(max) null
)
as
begin

	declare @delimiter varchar(30)

	set @delimiter = ', '

	declare @objectList TABLE
	(
		  id int not null identity(1,1)	
		, schemaName sysname  
		, objectName sysname
		, objectID int --as object_id(objectName)

	)

	insert into @objectList
	(schemaName, objectName, objectID)
	select 
			  tblSchema.name	
			, tblObject.name
			, tblObject.object_id
	from   sys.objects tblObject
				inner join sys.schemas tblSchema
					on tblObject.schema_id  = tblSchema.schema_id
	where  tblObject.type = 'U'

	insert into @listColumn
	(
		  schemaName
		, objectName
		, objectID
		, columnList
	 ) 

	select 
			  schemaName
			, objectName
			, objectID
			, (
				STUFF((
							select @delimiter + name
							
							from   sys.columns tblColumn
							
									inner join @objectList tblObjectList__Inner
							
										on tblColumn.object_id = tblObjectList__Inner.objectID
										and  tblObjectList__Inner.objectID = tblObjectList.objectID 
							
							and    
									--identity columns
									(
								
										
										(
											@excludeIdentityColumn = 0
										)
											
										or 
										(
												(@excludeIdentityColumn = 1)
											and (tblColumn.is_identity != 1)
										)
									)	
							order by tblColumn.column_id asc
						
							for XML PATH ('')
						), 1, 1, '')
							--as columnList		
		) as delimiterList

	from @objectList tblObjectList 	


	group by	
				  tblObjectList.schemaName	
				, tblObjectList.objectName
				, tblObjectList.objectID 
	
	return
				
end
go



Lab

Lab – SQLFiddle

A demo is available @ http://sqlfiddle.com/#!3/690d3/1

Here is a screen shot from SQLFiddle

ObjectCommaList -- SQLFiddle (v2)

Side Effects

One side effect of using Table Valued functions is that you do not get to see the Query\Execution plan.

And, so took it out to actually review the objects accessed; their statistics I/O, etc.

Here are the objects accessed:

Object Usage
sysclsobjs Objects
sysschobjs Schema
syscolpars Columns

References

References – Comma Delimited List

References – System Base Tables

References – Execution Plan

Leave a Reply

Please log in using one of these methods to post your comment:

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 )

Google+ photo

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

Connecting to %s