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:
select CAST( SERVERPROPERTY ('MachineName') as nvarchar(128) ) AS MachineName
, COALESCE ( CAST( SERVERPROPERTY ('InstanceName') as nvarchar(128) ) , 'Default') AS InstanceName
, db.name as DatabaseName
, db.crdate as CreateTs
, db.sid as LoginSid_DBOwner
, suser_sname(db.sid) as LoginName_DBOwner
, db.cmptlevel as 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' end as MergePublishedInd
, CASE cast ( DATABASEPROPERTYEX(db.name,'IsSubscribed') as varchar(255) )
WHEN '1' then 'Y' else 'N' end as SubscribedInd
, CASE cast ( DATABASEPROPERTYEX(db.name,'IsFulltextEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' end as FulltextEnabledInd
, CASE cast ( DATABASEPROPERTYEX(db.name,'IsInStandBy') as varchar(255) )
WHEN '1' then 'Y' else 'N' end as LogShipTargetInd
, COALESCE ( (select 'Y'
from msdb.dbo.log_shipping_primaries as log_shipping_primaries
where log_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' end as AutoCloseInd
, CASE cast ( DATABASEPROPERTYEX(db.name,'IsAutoCreateStatistics') as varchar(255) )
WHEN '1' then 'Y' else 'N' end as AutoCreateStatisticsInd
, CASE cast ( DATABASEPROPERTYEX(db.name,'IsAutoShrink') as varchar(255) )
WHEN '1' then 'Y' else 'N' end as AutoShrinkInd
, CASE cast ( DATABASEPROPERTYEX(db.name,'IsAutoUpdateStatistics') as varchar(255) )
WHEN '1' then 'Y' else 'N' end as AutoUpdateStatisticsInd
, CASE cast ( DATABASEPROPERTYEX(db.name,'IsRecursiveTriggersEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' end as RecursiveTriggersEnabledInd
, CASE cast ( DATABASEPROPERTYEX(db.name,'IsTornPageDetectionEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' end as TornPageDetectionEnabledInd
-- ANSI Behavior
, CASE cast ( DATABASEPROPERTYEX(db.name,'IsNullConcat') as varchar(255) )
WHEN '1' then 'Y' else 'N' end as NullConcatInd
, CASE cast ( DATABASEPROPERTYEX(db.name,'IsAnsiNullDefault') as varchar(255) )
WHEN '1' then 'Y' else 'N' end as AnsiNullDefaultInd
, CASE cast ( DATABASEPROPERTYEX(db.name,'IsAnsiNullsEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' end as AnsiNullsEnabledInd
, CASE cast ( DATABASEPROPERTYEX(db.name,'IsAnsiPaddingEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' end as AnsiPaddingEnabledInd
, CASE cast ( DATABASEPROPERTYEX(db.name,'IsAnsiWarningsEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' end as AnsiWarningsEnabledInd
, CASE cast ( DATABASEPROPERTYEX(db.name,'IsQuotedIdentifiersEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' end as QuotedIdentifiersEnabledInd
-- Numeric Behavior
, CASE cast ( DATABASEPROPERTYEX(db.name,'IsArithmeticAbortEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' end as ArithmeticAbortEnabledInd
, CASE cast ( DATABASEPROPERTYEX(db.name,'IsNumericRoundAbortEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' end as NumericRoundAbortEnabledInd
-- Cursors
, CASE cast ( DATABASEPROPERTYEX(db.name,'IsCloseCursorsOnCommitEnabled') as varchar(255) )
WHEN '1' then 'Y' else 'N' end as CloseCursorsOnCommitEnabledInd
, CASE cast ( DATABASEPROPERTYEX(db.name,'IsLocalCursorsDefault') as varchar(255) )
WHEN '1' then 'Y' else 'N' end as 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