----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-06-26 11:19:18
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--查询当前连接的活动(设置)的SET 选项
--法一:用DBCC查询已设置的内容,未显示项默认为OFF
DBCC USEROPTIONS;
/*
Set Option Value
---------------------------- ---------------------------
textsize 64512
language 简体中文
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
(13 行受影响)
DBCC 执行完毕。如果DBCC 输出了错误信息,请与系统管理员联系。
*/
--法二:通过以下代码查询
DECLARE @t TABLE ([值] [int],[配置] [nvarchar](30))
INSERT INTO @t
SELECT '1','DISABLE_DEF_CNST_CHK' UNION ALL
SELECT '2','IMPLICIT_TRANSACTIONS' UNION ALL
SELECT '4','CURSOR_CLOSE_ON_COMMIT' UNION ALL
SELECT '8','ANSI_WARNINGS' UNION ALL
SELECT '16','ANSI_PADDING' UNION ALL
SELECT '32','ANSI_NULLS' UNION ALL
SELECT '64','ARITHABORT' UNION ALL
SELECT '128','ARITHIGNORE' UNION ALL
SELECT '256','QUOTED_IDENTIFIER' UNION ALL
SELECT '512','NOCOUNT' UNION ALL
SELECT '1024','ANSI_NULL_DFLT_ON' UNION ALL
SELECT '2048','ANSI_NULL_DFLT_OFF' UNION ALL
SELECT '4096','CONCAT_NULL_YIELDS_NULL' UNION ALL
SELECT '8192','NUMERIC_ROUNDABORT' UNION ALL
SELECT '16384','XACT_ABORT'
SELECT [值],
[配置]+CASE
WHEN @@OPTIONS & [值] > 0 THEN ' ON'
ELSE ' OFF'
END AS 当前配置
FROM @t
/*
值 当前配置
----------- ----------------------------------
1 DISABLE_DEF_CNST_CHK OFF
2 IMPLICIT_TRANSACTIONS OFF
4 CURSOR_CLOSE_ON_COMMIT OFF
8 ANSI_WARNINGS ON
16 ANSI_PADDING ON
32 ANSI_NULLS ON
64 ARITHABORT ON
128 ARITHIGNORE OFF
256 QUOTED_IDENTIFIER ON
512 NOCOUNT OFF
1024 ANSI_NULL_DFLT_ON ON
2048 ANSI_NULL_DFLT_OFF OFF
4096 CONCAT_NULL_YIELDS_NULL ON
8192 NUMERIC_ROUNDABORT OFF
16384 XACT_ABORT OFF
(15 行受影响)
*/
相关参考:
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2010/06/26/5695584.aspx