Settings for All sprocs

• Bobby Glover

  SSCarpal Tunnel

  Points: 4143

  More actions

  March 28, 2013 at 3:48 am

  #272621

  I want to SET NOCOUNT ON

  For all Sprocs in my db.

  Is this a manual task or can this be performed via a script to apply to all sprocs.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  March 28, 2013 at 4:42 am

  #1601543

  Manual task.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• ESAT ERKEC

  SSCertifiable

  Points: 5661

  More actions

  March 28, 2013 at 4:54 am

  #1601549

  You can use this maybe

  CREATE TABLE #SPREPLACE (TXT VARCHAR(max))

  INSERT INTO #SPREPLACE EXEC SP_HELPTEXT 'Proc Name'

  DECLARE @SqlChangeScrpt AS VARCHAR(MAX)

  DECLARE @SqlChangeScrpt2 AS VARCHAR(MAX)

  DECLARE ChangeScrptCursor CURSOR FOR

  SELECT TXT FROM #SPREPLACE

  OPEN ChangeScrptCursor

  FETCH NEXT FROM ChangeScrptCursor INTO @SqlChangeScrpt

  SET @SqlChangeScrpt2=''

  WHILE @@FETCH_STATUS = 0

  BEGIN

  SELECT @SqlChangeScrpt2= @SqlChangeScrpt2+@SqlChangeScrpt

  FETCH NEXT FROM ChangeScrptCursor INTO @SqlChangeScrpt

  END

  CLOSE ChangeScrptCursor

  DEALLOCATE ChangeScrptCursor

  SELECT @SqlChangeScrpt2=REPLACE(@SqlChangeScrpt2,'CREATE PROC','ALTER PROC')

  ---SET ANOTHER THINKS TO PROC AND THEN ALTER PROC

  EXEC(@SqlChangeScrpt2)

  --DROP TABLE #SPREPLACE

• Ness

  SSCarpal Tunnel

  Points: 4282

  More actions

  March 28, 2013 at 5:42 am

  #1601567

  Manual is safer but.. I have used this in the past for setting an isolation level. You could adapt this

  --SET ALL SP's to BE READ COMMITTED

  SELECT

  o.OBJECT_ID

  , name

  ,REPLACE(REPLACE(REPLACE(s.definition, 'CREATE PROCEDURE', 'ALTER PROCEDURE'), 'AS ', +'AS '+CHAR(13)+'SET TRANSACTION ISOLATION LEVEL READ COMMITTED'+CHAR(13)+'--$History'+CHAR(13)), 'CREATE PROC','ALTER PROCEDURE')

  from sys.objects o

  JOIN sys.sql_modules s on s.object_id=o.object_id

  where name in (SELECT o.name from sys.objects o

  JOIN sys.sql_modules s on s.object_id=o.object_id

  join sys.procedures p on p.object_id=s.object_id )--Ensure Sproc

  Just ensure you test. The above has potential to fail depending on the structure of the Sprocs

  SQL DBA
  Every day is a school day, and don't trust anyone who tells you any different.
  http://sqlblogness.blogspot.co.uk