Is it possible to dynamically change Database properties?

Question

Is it possible to dynamically change Database properties?

fordie

SSC Journeyman

Points: 80
More actions
September 12, 2005 at 8:50 pm

#89026

I have written a wizard (.Net) to deploy a database.
However, my client's wish list now extends to importing database properties from an existing db or, having the ability to dynamically change the new db's properties.
I can easily list them but is it possible to programmatically change a database's properties?
Any advice much appreciated

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

in a nutshell, yes; you can change anything dynamically.

see sp_dboption in the BOL; also look in master and look at any of the stored frocs that start with sp_db or sp_msdb for starters.

here's the list from BOl for sp_dboption:

Option	Description
auto create statistics	When true, any missing statistics needed by a query for optimization are automatically built during optimization. For more information, see CREATE STATISTICS.
auto update statistics	When true, any out-of-date statistics needed by a query for optimization are automatically built during optimization. For more information, see UPDATE STATISTICS.
autoclose	When true, the database is shutdown cleanly and its resources are freed after the last user logs off.
autoshrink	When true, the database files are candidates for automatic periodic shrinking.
ANSI null default	When true, CREATE TABLE follows the SQL-92 rules to determine if a column allows null values.
ANSI nulls	When true, all comparisons to a null value evaluate to UNKNOWN. When false, comparisons of non-UNICODE values to a null value evaluate to TRUE if both values are NULL.
ANSI warnings	When true, errors or warnings are issued when conditions such as "divide by zero" occur.
arithabort	When true, an overflow or divide-by-zero error causes the query or batch to terminate. If the error occurs in a transaction, the transaction is rolled back. When false, a warning message is displayed, but the query, batch, or transaction continues as if no error occurred.
concat null yields null	When true, if either operand in a concatenation operation is NULL, the result is NULL.
cursor close on commit	When true, any cursors that are open when a transaction is committed or rolled back are closed. When false, such cursors remain open when a transaction is committed. When false, rolling back a transaction closes any cursors except those defined as INSENSITIVE or STATIC.
dbo use only	When true, only the database owner can use the database.
default to local cursor	When true, cursor declarations default to LOCAL.
merge publish	When true, the database can be published for a merge replication.
numeric roundabort	When true, an error is generated when loss of precision occurs in an expression. When false, losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result.
offline	When true, the database is offline.
published	When true, the database can be published for replication.
quoted identifier	When true, double quotation marks can be used to enclose delimited identifiers.
read only	When true, users can only read data in the database, not modify it. The database cannot be in use when a new value for the read only option is specified. The master database is the exception, and only the system administrator can use master while the read only option is being set.
recursive triggers	When true, enables recursive firing of triggers. When false, prevents direct recursion only. To disable indirect recursion, set the nested triggers server option to 0 using sp_configure.
select into/bulkcopy	When true, the SELECT INTO statement and fast bulk copies are allowed.
single user	When true, only one user at a time can access the database.
subscribed	When true, the database can be subscribed for publication.
torn page detection	When true, incomplete pages can be detected.
trunc. log on chkpt.	When true, a checkpoint truncates the inactive part of the log when the database is in log truncate mode. This is the only option you can set for the master database.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

fordie SSC Journeyman Points: 80 More actions · Answer 2

fordie

SSC Journeyman

Points: 80

September 13, 2005 at 5:33 pm

#589546

Excellent Lowell!

Thanks for your help

Viking-204480 SSC Eights! Points: 970 More actions · Answer 3

If you are using SQL Server 7.0, sp_dboption would apply but if SQL Server 2000 advised way of changing database properties is using ALTER DATABASE. Do watch for some important properties not to be set on, like AUTO SHRINK ON,..!

Thanks!

Viking