Generate a script with carriage return

Question

Post reply

Generate a script with carriage return

froshg

Valued Member

Points: 51
More actions
July 1, 2004 at 9:52 am

#111816

Hello,
I want generate an admin script in T-sql with SQL Analyzer but i've a problem !
[SQL ANALYZER or osql/isql]
select 'DBCC '+tablename+'+char(10)+char(13)+' GO ' from TableList
I want this :
DBCC TABLE1
GO
DBCC TABLE2
GO
...
but i've :
DBCC TABLE1 GO DBCC TABLE2 GO
could you give me the T-sql script to generate my scripts.
Regards
Guillaume.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Wayne-153714 Hall of Fame Points: 3536 More actions · Answer 1

This should do the job. The PRINT statement is better for this task than SELECT.

DECLARE C1 CURSOR

READ_ONLY

FOR SELECT tablename FROM TableList

DECLARE @tablename varchar(40)

OPEN C1

FETCH NEXT FROM C1 INTO @tablename

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

PRINT 'DBCC ' + @tablename + char(10) + char(13) + 'GO ' + char(10) + char(13)

END

FETCH NEXT FROM C1 INTO @tablename

END

CLOSE C1

DEALLOCATE C1

GO

When in doubt - test, test, test!

Wayne

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

too many '

here's the corrected version

select 'DBCC '

+tablename

+char(10)

+char(13)

+' GO ' from TableList

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

i did this

select 'DBCC '+name+char(10)+char(13)+' GO ' from sysobjects

and got

DBCC sysobjects

GO

DBCC sysindexes

GO

DBCC syscolumns

GO

DBCC systypes

GO

DBCC syscomments

MVDBA

ButchH Ten Centuries Points: 1073 More actions · Answer 4

Try char(13)+char(10)

I had trouble trying to concatenate things in a MS Access query where CRLF pairs were needed. I first tried using char(10)+char(13) (LFCR) and absolutely NOT getting what I wanted. as soon as I reversed them, it was PERFECT. Maybe that's why they're referred to as CRLF?

Hope this helps,

Butch

froshg Valued Member Points: 51 More actions · Answer 5

Hi,

Thanks for your answers.

I tested the various solutions and the only one which functions is as

follows:

DECLARE C1 CURSOR

READ_ONLY

FOR SELECT name FROM sysobjects

DECLARE @tablename varchar(40)

OPEN C1

FETCH NEXT FROM C1 INTO @tablename

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

PRINT 'DBCC ' + @tablename + char(10) + char(13) + 'GO ' + char(10) + char(13)

END

FETCH NEXT FROM C1 INTO @tablename

END

CLOSE C1

DEALLOCATE C1

GO

--------------- Edit ------

And for your information i use the SQL Analyzer utility.

---------------- end edit --------

Thanks

Alan Palmer Old Hand Points: 303 More actions · Answer 6

Try this instead but run it from an osql window.

select 'dbcc ' + tablename + convert(char(1),0xa) + 'go'

from tablelist

Much neater and easier - old sybase trick.

froshg Valued Member Points: 51 More actions · Answer 7

Hi,

It works fine from osql utility but no from wosql.

I've Two solutions now ! one from wosql with a cursor and a print and one with osql .

Thanks.