CODEWITHSUNDEEP
sql-serversql-server-2005sqlcmd
Gary Thomann
Gary Thomann

Reputation: 672

sqlcmd - How to display all the set option setting values

What is the command in sqlcmd to show all the set options and their associated settings?

For example how do you show the current value for nocount?

set
set nocount
set nocount ?

Upvotes: 1

Views: 3137

Answers (3)

James Greaves
James Greaves

Reputation: 1

DBCC USEROPTIONS (SQL 2008 and newer)

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-useroptions-transact-sql

Output: 

    textsize    2147483647
    language    us_english
    dateformat  mdy
    datefirst   7
    lock_timeout    -1
    quoted_identifier   SET
    arithabort  SET
    ansi_null_dflt_on   SET
    ansi_warnings   SET
    ansi_padding    SET
    ansi_nulls  SET
    concat_null_yields_null SET
    isolation level read committed

Upvotes: -1

chenson42
chenson42

Reputation: 1108

select 'DISABLE_DEF_CNST_CHK', case when (1 & @@options) = 1 then 1 else 0 end
union
select 'IMPLICIT_TRANSACTIONS', case when (2 & @@options) = 2 then 1 else 0 end
union
select 'CURSOR_CLOSE_ON_COMMIT', case when (4 & @@options) = 4 then 1 else 0 end
union
select 'ANSI_WARNINGS', case when (8 & @@options) = 8 then 1 else 0 end
union
select 'ANSI_PADDING', case when (16 & @@options) = 16 then 1 else 0 end
union
select 'ANSI_NULLS', case when (32 & @@options) = 32 then 1 else 0 end
union
select 'ARITHABORT', case when (64 & @@options) = 64 then 1 else 0 end
union
select 'ARITHIGNORE', case when (128 & @@options) = 128 then 1 else 0 end
union
select 'QUOTED_IDENTIFIER', case when (256 & @@options) = 256 then 1 else 0 end
union
select 'NOCOUNT', case when (512 & @@options) = 512 then 1 else 0 end
union
select 'ANSI_NULL_DFLT_ON', case when (1024 & @@options) = 1024 then 1 else 0 end
union
select 'ANSI_NULL_DFLT_OFF', case when (2048 & @@options) = 2048 then 1 else 0 end
union
select 'CONCAT_NULL_YIELDS_NULL', case when (4096 & @@options) = 4096 then 1 else 0 end
union
select 'NUMERIC_ROUNDABORT', case when (8192 & @@options) = 8192 then 1 else 0 end
union
select 'XACT_ABORT', case when (16384 & @@options) = 16384 then 1 else 0 end;

Upvotes: 2

Michał Powaga
Michał Powaga

Reputation: 23183

Determining SET Options for a Current Session in SQL Server

DECLARE @options INT

SELECT @options = @@OPTIONS

PRINT @options
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK'
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS'
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT'
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS'
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING'
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS'
IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT'
IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER'
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT'
IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON'
IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF'
IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL'
IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT'
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

Upvotes: 6

Related Questions