Apr 24, 2012

Sql script to check sql settings are on or off in sql server


Sql query or script to know or check nocount is on or off in sql server


SET NOCOUNT OFF

IF @@OPTIONS & 512  <> 512 BEGIN
    RAISERROR('Set nocount is off.',16,100)
END

Sql script to know quoted identifier is on or off in sql server

SET QUOTED_IDENTIFIER OFF

IF @@OPTIONS & 256  <> 256 BEGIN
    RAISERROR('Set nocount is off.',16,100)
END
   
   
Sql script to know arithabort is on or off in sql server

SET ARITHABORT OFF

IF @@OPTIONS & 64  <> 64 BEGIN
    RAISERROR('Set nocount is off.',16,100)
END
   
   
Sql script to know ansi nulls is on or off in sql server

SET ANSI_NULLS OFF

IF @@OPTIONS & 32  <> 32 BEGIN
    RAISERROR('Set nocount is off.',16,100)
END
   

Sql script to know ansi padding is on or off in sql server
   
SET ANSI_PADDING OFF

IF @@OPTIONS & 16  <> 16 BEGIN
    RAISERROR('Set nocount is off.',16,100)
END

Sql script to know ansi warnings is on or off in sql server
   
SET ANSI_WARNINGS OFF

IF @@OPTIONS &<> 8 BEGIN
    RAISERROR('Set nocount is off.',16,100)
END

Sql script to know cursor close on commit is on or off in sql server

SET CURSOR_CLOSE_ON_COMMIT OFF

IF @@OPTIONS &<> 4 BEGIN
    RAISERROR('Set nocount is off.',16,100)
END

Sql script to know implicit transactions is on or off in sql server

SET IMPLICIT_TRANSACTIONS OFF

IF @@OPTIONS &<> 2 BEGIN
    RAISERROR('Set nocount is off.',16,100)
END

1 comment: