Creating sql code within sql - carriage return - line feed 1

[DarwinIT]

I'm trying to create a little utility that will create all the update sets for a table. It works great except all of the set statements are on the same line. I want to put them each on a separate line. Here is the code.
Can I put characters into a select or return that will provided the formatting that I want???



DECLARE @tableName varchar(100)

set @tablename = 'mytable'

declare @ID int
select @id = id from sysobjects where name = @tablename and xtype = 'u'

if @id is null
BEGIN
raiserror ('Table not found ',16,1)
return
END

BEGIN
declare @list varchar (max)

select @list=isnull(@list + Name + ' = @' + Name + ', ' + char(13) + char(10) ,'')
from syscolumns
where id = @id

select @list as theList

end

 

[gmmastros]

Your code will have CR/LF in it. When you run the code above in a query window, you will not SEE the carriage return, line feeds because you are running in 'GRID' mode. If you change your mode to 'text' you will see it.

Right click the query window.
Click Results To -> Results To Text

Run the query again and you will see multiple lines.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DarwinIT]

Thank you very much. I have always used grid results. Never even crossed my mind that the grid was swallowing the new line characters. Works like a charm - except I need to alter the script to put brackets around the field names. Dude who created the database had a tendency to use reserved fields for Field names. Arggghhhh

 

[DarwinIT]

By the way - can the query result options be set in tsql?

 

[markros]

Check

http://social.msdn.microsoft.com/Fo...l/thread/3760241e-7019-486c-bb6b-89f0e60897a4

 

[DarwinIT]

Thanks, Markros!!

For anyone following this thread in the future - no programmatic way to set the results type - but keyboard shortcuts work CTRL T for text and CTRL D for grid.
That won't help if you need to set the column width to a larger number.