sysobjects - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

sysobjects

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2011-12-21 : 10:11:39

hi

how do i rewrite without using cursors or loops?

declare @ColName sysname,

@TableName sysname,

@FieldList varchar(MAX),
@LastTableName sysname

select @LastTableName = '',

@FieldList = ''

declare @tb table (TableName sysname null, FieldList varchar(MAX) null)
declare curs cursor for

select c.name, object_name(c.object_id)

from sys.columns c INNER JOIN sys.objects o on c.object_id = o.object_id

where o.type = 'U'

order by o.object_id

open curs

fetch curs into @ColName, @TableName

set @LastTableName = @TableName

while @@FETCH_STATUS = 0

BEGIN
if @LastTableName <> @TableName

BEGIN

insert into @tb values (@LastTableName,@FieldList)

set @FieldList = ''
set @LastTableName = @TableName

END

set @FieldList = case when @FieldList = '' then @ColName else + @FieldList + ',' + @ColName end

fetch curs into @ColName, @TableName

END

deallocate curs

insert into @tb values (@LastTableName,@FieldList)

select * from @tb

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-21 : 10:34:12

May be this?

SELECT DISTINCT
	c1.Table_Name,
	c3.columnNames
FROM
	Information_Schema.[COLUMNS] c1
	CROSS APPLY
	(
		SELECT 
			c2.column_name AS [text()],
			',' AS [text()]
		FROM
			Information_Schema.[COLUMNS] c2
		WHERE
			c1.Table_name = c2.TABLE_NAME
		FOR XML PATH('')
	) c3(columnNames)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources