Reading from sp_dboption
-
I'm working on a database that will hold information about our databases as part of an intranet site. Among other information, I want to store information about the database options that are enabled for each database in our environment.
I created the following stored procedure to run sp_dboption for a given server and database. The procedure works fine, but now I want to capture the output and insert it into my database.
The table in my database has several columns of the bit datatype, one for each possible database option. I am aiming to write a procedure that would read the output of sp_dboption and then update the appropriate column.
For example:
If sp_dboption returns "select into/bulkcopy", I want the procedure to read that and then insert a "1" in the appropriate bit column. Unfortunately, I don't see any easy way to do this in t-sql. I could always try to do this using vbscript on the webserver, but I would prefer not to do that.
Anyone have any ideas?
Ken
alter procedure ap_GetDatabaseOptions
@DatabaseName varchar(50),
@ServerName varchar(50)
as
begin
Declare @SQLCmd varchar(50)
Select @SQLCmd = 'exec ' + @ServerName + '.master..sp_dboption ' + @DatabaseName
Execute(@SQLCmd)
end
“If you're not outraged at the media, you haven't been paying attention.”
-
Although not what you asked for, this might give you some ideas.
/*
Name: Listet sämtliche Datenbank-Optionen auf
*/
SELECT LEFT(name,30) AS DB,
SUBSTRING(CASE status & 1 WHEN 0 THEN '' ELSE ',autoclose' END +
CASE status & 4 WHEN 0 THEN '' ELSE ',select into/bulk copy' END +
CASE status & 8 WHEN 0 THEN '' ELSE ',trunc. log on chkpt' END +
CASE status & 16 WHEN 0 THEN '' ELSE ',torn page detection' END +
CASE status & 32 WHEN 0 THEN '' ELSE ',loading' END +
CASE status & 64 WHEN 0 THEN '' ELSE ',pre-recovery' END +
CASE status & 128 WHEN 0 THEN '' ELSE ',recovering' END +
CASE status & 256 WHEN 0 THEN '' ELSE ',not recovered' END +
CASE status & 512 WHEN 0 THEN '' ELSE ',offline' END +
CASE status & 1024 WHEN 0 THEN '' ELSE ',read only' END +
CASE status & 2048 WHEN 0 THEN '' ELSE ',dbo USE only' END +
CASE status & 4096 WHEN 0 THEN '' ELSE ',single user' END +
CASE status & 32768 WHEN 0 THEN '' ELSE ',emergency mode' END +
CASE status & 4194304 WHEN 0 THEN '' ELSE ',autoshrink' END +
CASE status & 1073741824 WHEN 0 THEN '' ELSE ',cleanly shutdown' END +
CASE status2 & 16384 WHEN 0 THEN '' ELSE ',ANSI NULL default' END +
CASE status2 & 65536 WHEN 0 THEN '' ELSE ',concat NULL yields NULL' END +
CASE status2 & 131072 WHEN 0 THEN '' ELSE ',recursive triggers' END +
CASE status2 & 1048576 WHEN 0 THEN '' ELSE ',default TO local cursor' END +
CASE status2 & 8388608 WHEN 0 THEN '' ELSE ',quoted identifier' END +
CASE status2 & 33554432 WHEN 0 THEN '' ELSE ',cursor CLOSE on commit' END +
CASE status2 & 67108864 WHEN 0 THEN '' ELSE ',ANSI NULLs' END +
CASE status2 & 268435456 WHEN 0 THEN '' ELSE ',ANSI warnings' END +
CASE status2 & 536870912 WHEN 0 THEN '' ELSE ',full text enabled' END,
2,8000) AS Description
FROM master..sysdatabases
Do you think you need a table for this? Will options change frequently?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Can you post the bit to Option map that you are using?
ex. auto create statistics = Bit 1, etc
* Noel -
Here's the complete table design:
CREATE TABLE dbo.Databases (
ServerName varchar(50) not null,
DatabaseName varchar(50) null,
DatabaseCreationDate datetime null,
SQLVersion varchar(4) null,
DatabaseSize int null,
ANSINulls bit null,
ANSINullDefault bit null,
ANSIWarnings bit null,
ArithabortEnabled bit null,
AutoClose bit null,
AutoCreateStatistics bit null,
AutoShrink bit null,
AutoUpdateStatistics bit null,
ConcatNullYieldsNull bit null,
CursorCloseOnCommit bit null,
DBChaining bit null,
DBOUseOnly bit null,
DefaultToLocalCursor bit null,
MergePublish bit null,
NumericRoundabort bit null,
Published bit null,
QuotedIdentifier bit null,
ReadOnly bit null,
RecursiveTriggers bit null,
SelectIntoBulkCopy bit null,
SingleUser bit null,
Subscribed bit null,
TruncateLogOnCheckpoint bit null,
TornPageDetection bit null,
UseQuotedIdentifiers bit null,
)
“If you're not outraged at the media, you haven't been paying attention.”
-
Frank:
I was originally thinking of doing a direct query from sysdatabases as you do here, but it was difficult getting the values for all possible combinations of options. I may give your script a try to see if it gives me better results.
The database options don't change that frequently, but I have a lot of databases to add to this database and I was looking for a way to pull all of the information in one script.
Besides, I think it's to my benefit to learn more about how to chain procedures together.
Ken
“If you're not outraged at the media, you haven't been paying attention.”
-
That's why I've posted it. It shouldn't be too hard too modify it to update a table.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I believe that you will have to combine Frank's post with some DATABASEPROPERTYEX( database , property ) and update the table that way. I agree with frank it should not be difficult to change
* Noel -
Okay, I'm trying Frank's approach again. Now I'm having trouble writing a dynamic sql statement.
Here is the code:
Declare @DatabaseName varchar(50)
Declare @ServerName varchar(50)
Declare @SQLCmd char(29)
Declare @SQLCmd2 varchar(125)
Declare @SQLCmd3 varchar(61)
Declare @SQLCommand varchar(215)
Select @DatabaseName = 'FarmerJack'
Select @ServerName = 'MONF5473'
Select @SQLCmd = 'select name, status, status2 '
Select @SQLCmd2 = 'from ' + @ServerName + '.master.dbo.sysdatabases '
Select @SQLCmd3 = 'where name=' + @DatabaseName
Select @SQLCommand = @SQLCmd + @SQLCmd2 + @SQLCmd3
Execute(@SQLCommand)
When I run this, I get this error message:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'FarmerJack'.
I know I'm doing something wrong with the concatenation of the variable to the string, but I don't know what it is.
Thanks again.
“If you're not outraged at the media, you haven't been paying attention.”
-
Change
Select @DatabaseName = 'FarmerJack'
to
Select @DatabaseName = '''FarmerJack'''
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Oh, and by the way, Ken,
I really like the words in your autosignature.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks Frank, I got another variation from someone in my office:
Select @SQLCmd3 = 'where name = ''' + @DatabaseName + ''''
Glad you like my signature, Thomas Sowell is one of my favorite columists / authors.
“If you're not outraged at the media, you haven't been paying attention.”
-
Although I don't know hi, I think I would like him, too.
Another solution to escape this mess with quotation marks
SELECT QUOTENAME('FarmerJack','''')
One caveat with QUOTENAME is that the input string is an 128 or 129 nvarchar. But in your case plenty of space left.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply