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.

   

   

• Wayne-153714

  Hall of Fame

  Points: 3536

  More actions

  July 1, 2004 at 10:13 am

  #512991

  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

  July 1, 2004 at 10:15 am

  #512993

  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

  July 1, 2004 at 10:21 am

  #513000

  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

  July 1, 2004 at 10:27 pm

  #513068

  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

  July 2, 2004 at 1:23 am

  #513079

  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

  July 2, 2004 at 2:03 am

  #513084

  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

  July 5, 2004 at 2:29 am

  #513263

  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.