Database option - Pls help.

Hi all,
I wonder whether is there any system store procedure or any way for me to
display the database option ? I know I can use the MS SQL Server Management
Studio to view it but I want to use T-SQL statement.
Using sp_dboption I can see all database settable options but I can't see
the value, whether is it currently enable or disable ?
Thank you for your help! :D
Cheers,
Meng Soon Chua> Using sp_dboption I can see all database settable options but I can't see
> the value, whether is it currently enable or disable ?
To see the options that are currently set, pass the database name:
EXEC sp_dboption 'MyDatabase'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
news:70CE7261-172E-47D8-8598-63E8FDFD2D6C@.microsoft.com...
> Hi all,
> I wonder whether is there any system store procedure or any way for me to
> display the database option ? I know I can use the MS SQL Server
> Management
> Studio to view it but I want to use T-SQL statement.
> Using sp_dboption I can see all database settable options but I can't see
> the value, whether is it currently enable or disable ?
> Thank you for your help! :D
> Cheers,
> Meng Soon Chua|||Also note that sp_dboption was replaced with ALTER DATABASE in 2000. IF you are on 2005, I suggest
you do:
SELECT * FROM sys.databases WHERE name = 'Adventureworks'
If you are on 2000, I suggest you use the DATABASEPROPERTYEX() function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:850B4CAE-ACF2-4BAB-9585-9915E4644B4F@.microsoft.com...
>> Using sp_dboption I can see all database settable options but I can't see
>> the value, whether is it currently enable or disable ?
> To see the options that are currently set, pass the database name:
> EXEC sp_dboption 'MyDatabase'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
> news:70CE7261-172E-47D8-8598-63E8FDFD2D6C@.microsoft.com...
>> Hi all,
>> I wonder whether is there any system store procedure or any way for me to
>> display the database option ? I know I can use the MS SQL Server Management
>> Studio to view it but I want to use T-SQL statement.
>> Using sp_dboption I can see all database settable options but I can't see
>> the value, whether is it currently enable or disable ?
>> Thank you for your help! :D
>> Cheers,
>> Meng Soon Chua
>|||Hi Dan,
Thanks for the response.
Hey ! It does show those option that is turned on. But another question ;-)
I have checked the database (by using MS SQL Studio)
ANSI NULL Default -> False
ANSI NULLS Enabled -> False
Then next I start a 'New Query'
For this connection I write the below statement
USE Pubs
go
SET ANSI_NULLS ON
go
EXEC SP_DBOPTION 'Pubs'
--
The result display;
autoclose
auto create statistics
auto update statistics
Hmm ... how come the result didnt include 'ANSI NULLS Enabled' ? I thought I
turn it on for this connection ? Am I doing something wrong ? I just want to
double check whether I really turn on ANSI_NULLS :-| ...
Please help ? Many thanks ...
"Dan Guzman" wrote:
> > Using sp_dboption I can see all database settable options but I can't see
> > the value, whether is it currently enable or disable ?
> To see the options that are currently set, pass the database name:
> EXEC sp_dboption 'MyDatabase'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
> news:70CE7261-172E-47D8-8598-63E8FDFD2D6C@.microsoft.com...
> > Hi all,
> >
> > I wonder whether is there any system store procedure or any way for me to
> > display the database option ? I know I can use the MS SQL Server
> > Management
> > Studio to view it but I want to use T-SQL statement.
> > Using sp_dboption I can see all database settable options but I can't see
> > the value, whether is it currently enable or disable ?
> >
> > Thank you for your help! :D
> >
> > Cheers,
> > Meng Soon Chua
>|||> ANSI NULL Default -> False
> ANSI NULLS Enabled -> False
First, let me make it clear these settings are unrelated. "ANSI NULL
Default" setting affects default nullability of new columns and only when
both ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF are turned off. The Best
Practice is to explicitly specify NULL or NOT NULL in CREATE TABLE
statements so that you don't need to bother with that option.
The "ANSI NULLS Enabled" option controls how non-Unicode NULL values are
evaluated. The Best Practice is to always keep ANSI_NULL ON unless you have
a legacy application that can't be changed.
> Hmm ... how come the result didnt include 'ANSI NULLS Enabled' ? I thought
> I
> turn it on for this connection ? Am I doing something wrong ? I just want
> to
> double check whether I really turn on ANSI_NULLS :-| ...
Connection settings override the database default setting. When you check
the database options with sp_dboption (or DATABASEPROPEREX as Tibor
suggested), you are viewing only the database default setting for all
connections. You can execute DBCC USEROPTIONS to view the current
connection active settings.
Importantly, OLEDB, ODBC APIs turn on ANSI-92 behavior settings
automatically when you connect so there is usually no need to check these
manually. This also means that the related database setting defaults will
have no affect when ODBC and OLEDB clients connect.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
news:E96A8F93-EBD6-4865-A18E-D001FB7790A8@.microsoft.com...
> Hi Dan,
> Thanks for the response.
> Hey ! It does show those option that is turned on. But another question
> ;-)
> I have checked the database (by using MS SQL Studio)
> ANSI NULL Default -> False
> ANSI NULLS Enabled -> False
> Then next I start a 'New Query'
> For this connection I write the below statement
> USE Pubs
> go
> SET ANSI_NULLS ON
> go
> EXEC SP_DBOPTION 'Pubs'
> --
> The result display;
> autoclose
> auto create statistics
> auto update statistics
> Hmm ... how come the result didnt include 'ANSI NULLS Enabled' ? I thought
> I
> turn it on for this connection ? Am I doing something wrong ? I just want
> to
> double check whether I really turn on ANSI_NULLS :-| ...
> Please help ? Many thanks ...
>
> "Dan Guzman" wrote:
>> > Using sp_dboption I can see all database settable options but I can't
>> > see
>> > the value, whether is it currently enable or disable ?
>> To see the options that are currently set, pass the database name:
>> EXEC sp_dboption 'MyDatabase'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in
>> message
>> news:70CE7261-172E-47D8-8598-63E8FDFD2D6C@.microsoft.com...
>> > Hi all,
>> >
>> > I wonder whether is there any system store procedure or any way for me
>> > to
>> > display the database option ? I know I can use the MS SQL Server
>> > Management
>> > Studio to view it but I want to use T-SQL statement.
>> > Using sp_dboption I can see all database settable options but I can't
>> > see
>> > the value, whether is it currently enable or disable ?
>> >
>> > Thank you for your help! :D
>> >
>> > Cheers,
>> > Meng Soon Chua|||Hi ...
Thanks a lot guys. It does answer my question ;-)
"Dan Guzman" wrote:
> > ANSI NULL Default -> False
> > ANSI NULLS Enabled -> False
> First, let me make it clear these settings are unrelated. "ANSI NULL
> Default" setting affects default nullability of new columns and only when
> both ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF are turned off. The Best
> Practice is to explicitly specify NULL or NOT NULL in CREATE TABLE
> statements so that you don't need to bother with that option.
> The "ANSI NULLS Enabled" option controls how non-Unicode NULL values are
> evaluated. The Best Practice is to always keep ANSI_NULL ON unless you have
> a legacy application that can't be changed.
> > Hmm ... how come the result didnt include 'ANSI NULLS Enabled' ? I thought
> > I
> > turn it on for this connection ? Am I doing something wrong ? I just want
> > to
> > double check whether I really turn on ANSI_NULLS :-| ...
> Connection settings override the database default setting. When you check
> the database options with sp_dboption (or DATABASEPROPEREX as Tibor
> suggested), you are viewing only the database default setting for all
> connections. You can execute DBCC USEROPTIONS to view the current
> connection active settings.
> Importantly, OLEDB, ODBC APIs turn on ANSI-92 behavior settings
> automatically when you connect so there is usually no need to check these
> manually. This also means that the related database setting defaults will
> have no affect when ODBC and OLEDB clients connect.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in message
> news:E96A8F93-EBD6-4865-A18E-D001FB7790A8@.microsoft.com...
> > Hi Dan,
> >
> > Thanks for the response.
> > Hey ! It does show those option that is turned on. But another question
> > ;-)
> >
> > I have checked the database (by using MS SQL Studio)
> >
> > ANSI NULL Default -> False
> > ANSI NULLS Enabled -> False
> >
> > Then next I start a 'New Query'
> > For this connection I write the below statement
> > USE Pubs
> > go
> > SET ANSI_NULLS ON
> > go
> > EXEC SP_DBOPTION 'Pubs'
> >
> > --
> > The result display;
> >
> > autoclose
> > auto create statistics
> > auto update statistics
> >
> > Hmm ... how come the result didnt include 'ANSI NULLS Enabled' ? I thought
> > I
> > turn it on for this connection ? Am I doing something wrong ? I just want
> > to
> > double check whether I really turn on ANSI_NULLS :-| ...
> >
> > Please help ? Many thanks ...
> >
> >
> >
> > "Dan Guzman" wrote:
> >
> >> > Using sp_dboption I can see all database settable options but I can't
> >> > see
> >> > the value, whether is it currently enable or disable ?
> >>
> >> To see the options that are currently set, pass the database name:
> >>
> >> EXEC sp_dboption 'MyDatabase'
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Meng Soon Chua" <MengSoonChua@.discussions.microsoft.com> wrote in
> >> message
> >> news:70CE7261-172E-47D8-8598-63E8FDFD2D6C@.microsoft.com...
> >> > Hi all,
> >> >
> >> > I wonder whether is there any system store procedure or any way for me
> >> > to
> >> > display the database option ? I know I can use the MS SQL Server
> >> > Management
> >> > Studio to view it but I want to use T-SQL statement.
> >> > Using sp_dboption I can see all database settable options but I can't
> >> > see
> >> > the value, whether is it currently enable or disable ?
> >> >
> >> > Thank you for your help! :D
> >> >
> >> > Cheers,
> >> > Meng Soon Chua
> >>
>