Remove database name from CURSOR/

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  April 14, 2015 at 10:12 am

  #100581

  I have written a script to run DBCC CHECKDB, but I want to skip entering the database "master" when user runs the dbcc checkdb with "tablock=y" option enabled.

  Any generous person can please help me to complete my script?

  DECLARE database_cursor CURSOR FOR

  SELECT NAME

  FROM sys.databases db

  WHERE NAME NOT IN ( 'tempdb' )

  AND db.state_desc = 'ONLINE'

  AND source_database_id IS NULL

  -- REAL DBS ONLY (Not Snapshots)

  AND is_read_only = 0

  OPEN database_cursor

  FETCH next FROM database_cursor INTO @database_name

  WHILE @@FETCH_STATUS = 0

  BEGIN

  DECLARE @sql1 NVARCHAR(max)

  PRINT @database_name

  --SET @sql1 = '';

  SET @sql1 = 'DBCC CHECKDB(''' + @database_name + ''') WITH TABLERESULTS';

  IF @PHYSICAL_ONLY = 'Y'

  SET @sql1 = @sql1 + ', PHYSICAL_ONLY '

  IF @tablock = 'Y'

  SET @sql1 = @sql1 + ', TABLOCK '

  SET @sql1 = 'DBCC CHECKDB(''' + @database_name + ''') WITH TABLERESULTS';

  IF @allMessages = 'Y'

  SET @sql1 = @sql1 + ', ALL_ERRORMSGS '

  IF @PHYSICAL_ONLY = 'Y' and @tablock = 'Y'

  BEGIN

  PRINT 'WRONG OPTION. You can''t choose both @PHYSICAL_ONLY and @tablock Options in the DBCC CHECKDB Syntax..'

  --SET @sql1 = 'DBCC CHECKDB(''' + @database_name + ''') WITH TABLERESULTS, ALL_ERRORMSGS';

  --SET @sql1 = 'DBCC CHECKDB(''' + @database_name + ''') WITH TABLERESULTS, ALL_ERRORMSGS';

  RETURN 1;

  END

  SELECT @sql1

  INSERT INTO dbcc_history ([Error], [Level], [State], MessageText, RepairLevel, [Status],

  [DbId], [DbFragId],[ObjectId], [IndexId], PartitionId, AllocUnitId,[RidDbid], [RidPruid],[File],

  Page, Slot, [RefDbId], [RefPruId], RefFile, RefPage,

  RefSlot,Allocation)

  EXEC(@sql1)

  FETCH next FROM database_cursor INTO @database_name

  END

  -- Check data in staging table.

  --SELECT error,

  -- level,

  -- Db_name(dbid) AS DBName,

  -- Object_name(objectid, dbid) AS ObjectName,

  -- messagetext,

  -- timestamp

  --FROM dbcc_history

  CLOSE DATABASE_CURSOR

  DEALLOCATE DATABASE_CURSOR

  END

  Thanks.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736270

  More actions

  April 14, 2015 at 10:47 am

  #1790901

  You have the name in the variable you set from the cursor. Use an IF statement to check if it's master and then skip the rest of your loop script.