INVENTORY SCRIPTS : DATABASES (with flags for mirroring and log shipping)
- I am sorry about the poor formatting and lack of attachments, blogger.com does not allow this.
- Seel also companion scripts with Mirroring and Log Shipping Details
- The script can be run against SQL 2008 R2, 2008, 2005, and 2000. The script is run as a dynamic query so SQL 2000 boxes will not error out on the tables it cannot find.
IF Object_id('tempdb..#Databases') IS NOT NULL
DROP TABLE #databases
CREATE TABLE #databases
(
[database_id] [INT] NULL,
[Database_Name] [SYSNAME] NULL,
[SnapshotSource_Database_ID] [INT] NULL,
[state] [TINYINT] NULL,
[state_desc] [NVARCHAR](60) NULL,
[recovery_model] [TINYINT] NULL,
[recovery_model_desc] [NVARCHAR](60) NULL,
[owner_sid] [VARBINARY](85) NULL,
[create_date] [DATETIME] NULL,
[compatibility_level] [TINYINT] NULL,
[collation_name] [SYSNAME] NULL,
[user_access] [TINYINT] NULL,
[user_access_desc] [NVARCHAR](60) NULL,
[is_cleanly_shutdown] [BIT] NULL,
[is_supplemental_logging_enabled] [BIT] NULL,
[snapshot_isolation_state] [TINYINT] NULL,
[snapshot_isolation_state_desc] [NVARCHAR](60) NULL,
[is_read_committed_snapshot_on] [BIT] NULL,
[page_verify_option] [TINYINT] NULL,
[page_verify_option_desc] [NVARCHAR](60) NULL,
[Is_Mirrored] [BIT] NULL,
[Is_Mirror] [BIT] NULL,
[Is_LogShipped_Primary] [BIT] NULL,
[Is_LogShipped_Secondary] [BIT] NULL,
[Is_Snapshot] [BIT] NULL,
[is_read_only] [BIT] NULL,
[is_in_standby] [BIT] NULL,
[is_auto_close_on] [BIT] NULL,
[is_auto_shrink_on] [BIT] NULL,
[is_auto_create_stats_on] [BIT] NULL,
[is_auto_update_stats_on] [BIT] NULL,
[is_auto_update_stats_async_on] [BIT] NULL,
[is_ansi_null_default_on] [BIT] NULL,
[is_ansi_nulls_on] [BIT] NULL,
[is_ansi_padding_on] [BIT] NULL,
[is_ansi_warnings_on] [BIT] NULL,
[is_arithabort_on] [BIT] NULL,
[is_concat_null_yields_null_on] [BIT] NULL,
[is_numeric_roundabort_on] [BIT] NULL,
[is_quoted_identifier_on] [BIT] NULL,
[is_recursive_triggers_on] [BIT] NULL,
[is_cursor_close_on_commit_on] [BIT] NULL,
[is_local_cursor_default] [BIT] NULL,
[is_fulltext_enabled] [BIT] NULL,
[is_trustworthy_on] [BIT] NULL,
[is_db_chaining_on] [BIT] NULL,
[is_parameterization_forced] [BIT] NULL,
[is_master_key_encrypted_by_server] [BIT] NULL,
[is_published] [BIT] NULL,
[is_subscribed] [BIT] NULL,
[is_merge_published] [BIT] NULL,
[is_distributor] [BIT] NULL,
[is_sync_with_backup] [BIT] NULL,
[service_broker_guid] [UNIQUEIDENTIFIER] NULL,
[is_broker_enabled] [BIT] NULL,
[log_reuse_wait] [TINYINT] NULL,
[log_reuse_wait_desc] [NVARCHAR](60) NULL,
[is_date_correlation_on] [BIT] NULL,
[is_ChangeTracking_Enabled] [BIT] NULL,
[is_encrypted] [BIT] NULL,
[is_honor_broker_priority_on] [BIT] NULL
)
ON [PRIMARY]
IF (SELECT CAST(LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(128)),
Charindex('.', CAST(
Serverproperty('ProductVersion'
) AS
VARCHAR(
128))) - 1) AS INT)) IN ( 10 )
BEGIN
/*********************************************************
START : SQL 2008
*********************************************************/
EXECUTE (' INSERT INTO #Databases SELECT [Databases].[database_id] ,[name] as [Database_Name] ,[source_database_id] as [SnapshotSource_Database_ID] ,[state] ,[state_desc] ,[recovery_model] ,[recovery_model_desc] ,[owner_sid] ,[create_date] ,[compatibility_level] ,[collation_name] ,[user_access] ,[user_access_desc] ,[is_cleanly_shutdown] ,[is_supplemental_logging_enabled] ,[snapshot_isolation_state] ,[snapshot_isolation_state_desc] ,[is_read_committed_snapshot_on] ,[page_verify_option] ,[page_verify_option_desc] ,CASE WHEN [Mirroring].[mirroring_role_desc] = ''PRINCIPAL'' THEN 1 ELSE 0 END [Is_Mirrored] ,CASE WHEN [Mirroring].[mirroring_role_desc] = ''MIRROR'' THEN 1 ELSE 0 END [Is_Mirror] ,CASE WHEN [LogShipping].[Primary_Database] IS NOT NULL THEN 1 ELSE 0 END [Is_LogShipped_Primary] ,CASE WHEN [LogShipping_Secondary].[Primary_Database] IS NOT NULL THEN 1 ELSE 0 END [Is_LogShipped_Secondary] ,CASE WHEN [source_database_id] IS NOT NULL THEN 1 ELSE 0 END [Is_SnapShot] ,[is_read_only] ,[is_in_standby] ,[is_auto_close_on] ,[is_auto_shrink_on] ,[is_auto_create_stats_on] ,[is_auto_update_stats_on] ,[is_auto_update_stats_async_on] ,[is_ansi_null_default_on] ,[is_ansi_nulls_on] ,[is_ansi_padding_on] ,[is_ansi_warnings_on] ,[is_arithabort_on] ,[is_concat_null_yields_null_on] ,[is_numeric_roundabort_on] ,[is_quoted_identifier_on] ,[is_recursive_triggers_on] ,[is_cursor_close_on_commit_on] ,[is_local_cursor_default] ,[is_fulltext_enabled] ,[is_trustworthy_on] ,[is_db_chaining_on] ,[is_parameterization_forced] ,[is_master_key_encrypted_by_server] ,[is_published] ,[is_subscribed] ,[is_merge_published] ,[is_distributor] ,[is_sync_with_backup] ,[service_broker_guid] ,[is_broker_enabled] ,[log_reuse_wait] ,[log_reuse_wait_desc] ,[is_date_correlation_on] --------------------------------------------------------------------------------------------------------------- ,[is_cdc_enabled] as [is_ChangeTracking_Enabled] ,[is_encrypted] ,[is_honor_broker_priority_on] FROM [master].[sys].[databases] [Databases] LEFT OUTER JOIN [master].[sys].[database_mirroring] [Mirroring] On [Mirroring].[database_id] = [Databases].[database_id] LEFT OUTER JOIN [msdb].[dbo].[log_shipping_monitor_primary] [LogShipping] ON [LogShipping].[Primary_Database] = [Databases].[name] LEFT OUTER JOIN [msdb].[dbo].[log_shipping_secondary] [LogShipping_Secondary] ON [LogShipping_Secondary].[Primary_Database] = [Databases].[name] ')
/*********************************************************
END : SQL 2008
*********************************************************/
END
ELSE
IF (SELECT CAST(LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(128)),
Charindex('.', CAST(
Serverproperty('ProductVersion')
AS
VARCHAR(128))) - 1) AS INT)) IN ( 9 )
BEGIN
/*********************************************************
START : SQL 2005
*********************************************************/
EXECUTE (' INSERT INTO #Databases SELECT [Databases].[database_id] ,[name] as [Database_Name] ,[source_database_id] as [SnapshotSource_Database_ID] ,[state] ,[state_desc] ,[recovery_model] ,[recovery_model_desc] ,[owner_sid] ,[create_date] ,[compatibility_level] ,[collation_name] ,[user_access] ,[user_access_desc] ,[is_cleanly_shutdown] ,[is_supplemental_logging_enabled] ,[snapshot_isolation_state] ,[snapshot_isolation_state_desc] ,[is_read_committed_snapshot_on] ,[page_verify_option] ,[page_verify_option_desc] ,CASE WHEN [Mirroring].[mirroring_role_desc] = ''PRINCIPAL'' THEN 1 ELSE 0 END [Is_Mirrored] ,CASE WHEN [Mirroring].[mirroring_role_desc] = ''MIRROR'' THEN 1 ELSE 0 END [Is_Mirror] ,CASE WHEN [LogShipping].[Primary_Database] IS NOT NULL THEN 1 ELSE 0 END [Is_LogShipped_Primary] ,CASE WHEN [LogShipping_Secondary].[Primary_Database] IS NOT NULL THEN 1 ELSE 0 END [Is_LogShipped_Secondary] ,CASE WHEN [source_database_id] IS NOT NULL THEN 1 ELSE 0 END [Is_SnapShot] ,[is_read_only] ,[is_in_standby] ,[is_auto_close_on] ,[is_auto_shrink_on] ,[is_auto_create_stats_on] ,[is_auto_update_stats_on] ,[is_auto_update_stats_async_on] ,[is_ansi_null_default_on] ,[is_ansi_nulls_on] ,[is_ansi_padding_on] ,[is_ansi_warnings_on] ,[is_arithabort_on] ,[is_concat_null_yields_null_on] ,[is_numeric_roundabort_on] ,[is_quoted_identifier_on] ,[is_recursive_triggers_on] ,[is_cursor_close_on_commit_on] ,[is_local_cursor_default] ,[is_fulltext_enabled] ,[is_trustworthy_on] ,[is_db_chaining_on] ,[is_parameterization_forced] ,[is_master_key_encrypted_by_server] ,[is_published] ,[is_subscribed] ,[is_merge_published] ,[is_distributor] ,[is_sync_with_backup] ,[service_broker_guid] ,[is_broker_enabled] ,[log_reuse_wait] ,[log_reuse_wait_desc] ,[is_date_correlation_on] --------------------------------------------------------------------------------------------------------------- ,NULL as [is_cdc_enabled] as [is_ChangeTracking_Enabled] ,NULL as [is_encrypted] ,NULL as [is_honor_broker_priority_on] FROM [master].[sys].[databases] [Databases] LEFT OUTER JOIN [master].[sys].[database_mirroring] [Mirroring] On [Mirroring].[database_id] = [Databases].[database_id] LEFT OUTER JOIN [msdb].[dbo].[log_shipping_monitor_primary] [LogShipping] ON [LogShipping].[Primary_Database] = [Databases].[name] LEFT OUTER JOIN [msdb].[dbo].[log_shipping_secondary] [LogShipping_Secondary] ON [LogShipping_Secondary].[Primary_Database] = [Databases].[name] ')
/*********************************************************
END : SQL 2000 or Lower
*********************************************************/
END
ELSE
BEGIN
/*********************************************************
START : SQL 2000 or Lower
*********************************************************/
DECLARE @DataBase VARCHAR(512)
SET @Database = ''
WHILE ( @Database IS NOT NULL )
BEGIN
SET @Database = (SELECT MIN(name)
FROM [master].[dbo].[sysdatabases]
WHERE name > @Database)
/****** START LOOP ********/
IF Len(@Database) > 0
BEGIN
EXEC (' INSERT INTO #Databases ([Database_ID],[Database_Name],[state_desc],[recovery_model_desc],[create_date]) SELECT dbid as [Database_ID] ,[Databases].[Name] as [DataBase_Name] ,CONVERT(sysname,DatabasePropertyEx(''' +
@Database
+
''',''Status'')) AS state_desc ,CONVERT(sysname,DatabasePropertyEx(''' +
@Database
+
''',''Recovery'')) AS recovery_model_desc ,CrDate as [create_date] FROM [master].dbo.sysDatabases [Databases] WHERE [Name] = ''' + @Database + ''' ')
END
/****** END LOOP **********/
END
/*********************************************************
END : SQL 2000 or Lower
*********************************************************/
END
SELECT *
FROM #databases
DROP TABLE #databases
No comments:
Post a Comment