SQL to output SQL

Question

Post reply

SQL to output SQL

MrBaseball34

SSCrazy

Points: 2515
More actions
January 29, 2009 at 7:55 am

#134091

I am trying to write SQL to write the SQL to alter all columns in a DB from char to varchar.
DECLARE @CMD varchar(max), @CRLF char(2)
SET @CRLF = char(10) + char(13)
SET @CMD = (SELECT 'ALTER TABLE '
+ t.name
+ ' ALTER COLUMN '
+ c.name
+ '[varchar] (' + Convert(varchar,c.max_length) + ') '
+ CASE WHEN c.is_nullable = 1 THEN
'NULL'
ELSE
'NOT NULL'
END
+ @CRLF + 'GO' + @CRLF
FROM sys.columns c
INNER JOIN sys.tables t
ON t.object_id = c.object_id
WHERE c.system_type_id = 175)
SELECT @CMD
EXEC sp_executesql @CMD
Having problems getting this done. Can anyone help out?
Also, is there a way to skip the ones that have a constraint?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18856 More actions · Answer 1

After briefly scanning the post:

1. GO is not a SQL command but a batch terminator in the tools. Try getting rid of it.

2. @CRLF should be char(13) + char(10)

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 2

Ken McKelvey (1/29/2009)
After briefly scanning the post:
1. GO is not a SQL command but a batch terminator in the tools. Try getting rid of it.

I want a GO after each command.

Ken McKelvey (1/29/2009)
2. @CRLF should be char(13) + char(10)

Noted...

However, this is the error I get:

Msg 512, Level 16, State 1, Line 3

Subquery returned more than 1 value. This is not permitted when the subquery follows =,

!=, = or when the subquery is used as an expression.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Your code is all wrong. I am looking for an example of something I have done here at work, I'm just having a problem locating the script I want.

Stay tuned, hopefully I'll be back ...

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 4

Thanks, Lynn. I'd like to see what the problem is..

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Here is some code (that I know Jeff will start shooting pork chops at for having written as it uses a while loop).

Wouldn't let me post directly, so had to upload it in a file.

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 6

ok, cool..

How would I check to see if the column has a constraint that I must remove, first?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Two places, INFORMATION_SCHEMA views or the system views (not sure which ones off the top of my head).

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 8

I actually have it working for all columns but for some reason, the char(13) + char(10) doesn't input a CRLF in the text when I paste it into a text editor after copying from the results grid.

Any ideas?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

MrBaseball34 (1/29/2009)
I actually have it working for all columns but for some reason, the char(13) + char(10) doesn't input a CRLF in the text when I paste it into a text editor after copying from the results grid.
Any ideas?

No idea. I have had no problems with that. I have posted the generated code in both SSMS and UltraEdit with out any difficulties.