January 4, 2008 at 10:53 am
Good day,
I am trying to find all the places where a setting may have been set wrong...The auto_update_stats in sql 2000 DB's. I wrote the following which gets me data for sql 2005:
SELECT
NAME,
recovery_model_desc,
CASE is_auto_update_stats_on
WHEN 1 THEN 'ON'
WHEN 0 THEN 'OFF'
ELSE 'UNKNOWN'
END AS is_auto_update_stats_on,
CASE is_auto_update_stats_async_on
WHEN 1 THEN 'ON'
WHEN 0 THEN 'OFF'
ELSE 'UNKNOWN'
END AS is_auto_update_stats_async_on
FROM sys.databases
What would I do to find this in a sql 2000?
-- Cory
January 4, 2008 at 1:06 pm
I wrote this script to find the recovery model of all databases on any server. But you can modify to your convenience to get the result you wanted.
Use master
GO
declare @DBName varchar(35),
@STR varchar (255)
declare RecoveryModel cursor for
select name from sysdatabases
where category in ('0', '1','16')
order by name
open RecoveryModel
fetch next from RecoveryModel into @DBName while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
select @STR = 'SELECT DATABASEPROPERTYEX ('''+ @DBName + ''', ''Recovery'')' + @DBName
exec (@str)
end fetch next from RecoveryModel into @DBName end
close RecoveryModel
DEALLOCATE RecoveryModel
go
SQL DBA.
January 4, 2008 at 1:34 pm
Here some SQL to list all of the database properties:
selectCAST( SERVERPROPERTY ('MachineName') as nvarchar(128) )AS MachineName
,COALESCE ( CAST( SERVERPROPERTY ('InstanceName') as nvarchar(128) ) , 'Default')AS InstanceName
,db.name as DatabaseName
,db.crdateas CreateTs
,db.sidas LoginSid_DBOwner
,suser_sname(db.sid) as LoginName_DBOwner
,db.cmptlevelas SQLServerBuildId
,COALESCE ( cast ( DATABASEPROPERTYEX(db.name,'Collation') as varchar(255) ) , 'UNKNOWN' )as CollationName
,cast ( DATABASEPROPERTYEX(db.name,'Recovery') as varchar(255) ) as RecoveryModeName
,COALESCE ( cast ( DATABASEPROPERTYEX(db.name,'SQLSortOrder') as varchar(255) ) , 'UNKNOWN' )as SQLSortOrderName
,cast ( DATABASEPROPERTYEX(db.name,'Status') as varchar(255) ) as StatusName
,cast ( DATABASEPROPERTYEX(db.name,'Updateability') as varchar(255) ) as UpdateabilityName
,cast ( DATABASEPROPERTYEX(db.name,'UserAccess') as varchar(255) ) as UserAccessName
-- Replication
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsMergePublished') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas MergePublishedInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsSubscribed') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas SubscribedInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsFulltextEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas FulltextEnabledInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsInStandBy') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas LogShipTargetInd
,COALESCE ( (select 'Y'
from msdb.dbo.log_shipping_primariesas log_shipping_primaries
wherelog_shipping_primaries.primary_database_name = db.name
) , 'N') as LogShipSourceInd
-- Automatice Behaviors
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsAutoClose') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas AutoCloseInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsAutoCreateStatistics') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas AutoCreateStatisticsInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsAutoShrink') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas AutoShrinkInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsAutoUpdateStatistics') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas AutoUpdateStatisticsInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsRecursiveTriggersEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas RecursiveTriggersEnabledInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsTornPageDetectionEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas TornPageDetectionEnabledInd
-- ANSI Behavior
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsNullConcat') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas NullConcatInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsAnsiNullDefault') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas AnsiNullDefaultInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsAnsiNullsEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas AnsiNullsEnabledInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsAnsiPaddingEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas AnsiPaddingEnabledInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsAnsiWarningsEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas AnsiWarningsEnabledInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsQuotedIdentifiersEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas QuotedIdentifiersEnabledInd
-- Numeric Behavior
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsArithmeticAbortEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas ArithmeticAbortEnabledInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsNumericRoundAbortEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas NumericRoundAbortEnabledInd
-- Cursors
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsCloseCursorsOnCommitEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas CloseCursorsOnCommitEnabledInd
,CASE cast ( DATABASEPROPERTYEX(db.name,'IsLocalCursorsDefault') as varchar(255) )
WHEN '1' then 'Y' else 'N' endas LocalCursorsDefaultInd
from master.dbo.sysdatabases db
SQL = Scarcely Qualifies as a Language
January 4, 2008 at 2:58 pm
Thanks Carl. Cool script. Definitely worth saving in personal briefcase.
SQL DBA.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply