SQL Server 2012:
set nocount on;
/*Virtual Machine Check*/
declare @virtual_machine_type_desc varchar(50)
declare @Server_type varchar(50)
select
@virtual_machine_type_desc = dosi.virtual_machine_type_desc
,@Server_type =
CASE
WHEN dosi.virtual_machine_type = 1
THEN ‘Virtual’
ELSE ‘Physical’
END
FROM sys.dm_os_sys_info dosi
select @virtual_machine_type_desc as virtual_machine_type_desc,@Server_type as Server_type
/* ALWAYSON Check */
DECLARE @ALWAYSON_INFO_TABLE TABLE (ALWAYSON_STATUS XML)
DECLARE @ALWAYSON_STATUS XML
INSERT INTO@ALWAYSON_INFO_TABLE (ALWAYSON_STATUS)
SELECT
(
SELECT
AGC.name — Availability Group
,RCS.replica_server_name — SQL cluster node name
,ARS.role_desc — Replica Role
,AGL.dns_name — Listener Name
FROM
sys.availability_groups_cluster ASAGC
INNERJOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
ON
RCS.group_id = AGC.group_id
INNERJOIN sys.dm_hadr_availability_replica_states AS ARS
ON
ARS.replica_id = RCS.replica_id
INNERJOIN sys.availability_group_listeners AS AGL
ON
AGL.group_id = ARS.group_id
WHERE
ARS.role_desc = ‘PRIMARY’
ORDERBY 1,2
FOR XML PATH(”)
)
SELECT @ALWAYSON_STATUS =ALWAYSON_STATUS FROM @ALWAYSON_INFO_TABLE
–SELECT @ALWAYSON_STATUS
/* Logshipping Check */
DECLARE @LOGSHIPPING_PRIMARYSECONDARY_TABLE TABLE (LOGSHIPPING_PRIMARYSECONDARY XML)
DECLARE @LOGSHIPPING_PRIMARYSECONDARY XML
INSERT INTO@LOGSHIPPING_PRIMARYSECONDARY_TABLE (LOGSHIPPING_PRIMARYSECONDARY)
SELECT
(
SELECT lp.primary_server,LS.secondary_server, sd.name,
casewhen lp.primary_database IS null then ‘No’ else ‘Yes’ end LSPConfigured,
casewhen ls.secondary_database IS null then ‘No’ else ‘Yes’ end LSSConfigured
From
Master.dbo.sysDatabases SD
leftjoin msdb.dbo.log_shipping_monitor_primary LP on lp.primary_database=SD.name
leftjoin msdb.dbo.log_shipping_monitor_secondary LS on LS.secondary_database=SD.name
where (lp.primary_server is not null or LS.secondary_server is not null)
FOR XML PATH(”)
)
SELECT @LOGSHIPPING_PRIMARYSECONDARY =LOGSHIPPING_PRIMARYSECONDARY FROM@LOGSHIPPING_PRIMARYSECONDARY_TABLE
–SELECT @LOGSHIPPING_PRIMARYSECONDARY AS LOGSHIPPING_PRIMARYSECONDARY
/*Mirroring Status:*/
DECLARE @MIRRORING_STATUS_TABLE TABLE (MIRRORING_STATUS XML)
DECLARE @MIRRORING_STATUS XML
INSERT INTO@MIRRORING_STATUS_TABLE (MIRRORING_STATUS)
SELECT
(
SELECT
DISTINCT Name,DB_in_Mirror,Mirroring_Role
FROM
(
SELECT d.name
,f.physical_name
,f.type_desc
,DB_in_Mirror=COALESCE(m.mirroring_role_desc,‘Not Part in Mirror’) –database must not be part of mirror (neither as principal nor mirror) in order to be detached
,[Size (Gb)]=CAST(f.size/1024.00/1024.00 AS NUMERIC(18,2))
,COALESCE(m.mirroring_role,0) AS Mirroring_Role
FROMsys.databases d
INNERJOIN SYS.master_files f
ON d.database_id = f.database_id
LEFTOUTER JOIN sys.database_mirroring m
ON d.database_id = m.database_id
where 1 = 1
ANDd.state = 0 — online
)X
WHEREX.DB_in_Mirror <>‘Not Part in Mirror’
ORDERBY 1,2
FOR XML PATH(”)
)
SELECT @MIRRORING_STATUS =MIRRORING_STATUS FROM @MIRRORING_STATUS_TABLE
–SELECT @MIRRORING_STATUS
/*Replication Publisher Status*/
DECLARE @REPLICATION_PUBLISHER_TABLE TABLE (REPLICATION_PUBLISHER XML)
DECLARE @REPLICATION_PUBLISHER XML
INSERT INTO@REPLICATION_PUBLISHER_TABLE (REPLICATION_PUBLISHER)
SELECT
(
SELECT
X.name,
is_published
FROM
(
SELECTDISTINCT d.name
,d.is_published
,d.is_subscribed
,d.is_merge_published
,d.is_distributor
FROMsys.databases d
INNERJOIN SYS.master_files f
ON d.database_id = f.database_id
where 1 = 1
AND
d.state = 0 — online
)X
WHEREX.is_published =1
GROUPBY X.NAME,X.is_published
ORDERBY 1,2
FOR XML PATH(”)
)
SELECT @REPLICATION_PUBLISHER =REPLICATION_PUBLISHER FROM@REPLICATION_PUBLISHER_TABLE
/*Replication Subscriber Status*/
DECLARE @REPLICATION_SUBSCRIBER_TABLE TABLE (REPLICATION_SUBSCRIBER XML)
DECLARE @REPLICATION_SUBSCRIBER XML
INSERT INTO@REPLICATION_SUBSCRIBER_TABLE (REPLICATION_SUBSCRIBER)
SELECT
(
SELECT
X.name,
is_subscribed
FROM
(
SELECTDISTINCT d.name
,d.is_published
,d.is_subscribed
,d.is_merge_published
,d.is_distributor
FROMsys.databases d
INNERJOIN SYS.master_files f
ON d.database_id = f.database_id
where 1 = 1
AND
d.state = 0 — online
)X
WHEREX.is_subscribed =1
GROUPBY X.NAME,X.is_subscribed
ORDERBY 1,2
FOR XML PATH(”)
)
SELECT @REPLICATION_SUBSCRIBER = REPLICATION_SUBSCRIBER FROM@REPLICATION_SUBSCRIBER_TABLE
/*Replication Merge Publisher Status*/
DECLARE @REPLICATION_MERGE_PUBLISHER_TABLE TABLE (REPLICATION_MERGE_PUBLISHER XML)
DECLARE @REPLICATION_MERGE_PUBLISHER XML
INSERT INTO@REPLICATION_MERGE_PUBLISHER_TABLE (REPLICATION_MERGE_PUBLISHER)
SELECT
(
SELECT
X.name,
is_merge_published
FROM
(
SELECTDISTINCT d.name
,d.is_published
,d.is_subscribed
,d.is_merge_published
,d.is_distributor
FROMsys.databases d
INNERJOIN SYS.master_files f
ON d.database_id = f.database_id
where 1 = 1
AND
d.state = 0 — online
)X
WHEREX.is_merge_published =1
GROUPBY X.NAME,X.is_merge_published
ORDERBY 1,2
FOR XML PATH(”)
)
SELECT @REPLICATION_MERGE_PUBLISHER = REPLICATION_MERGE_PUBLISHER FROM @REPLICATION_MERGE_PUBLISHER_TABLE
/*Replication Distributor Status*/
DECLARE @REPLICATION_DISTRIBUTOR_TABLE TABLE (REPLICATION_DISTRIBUTOR XML)
DECLARE @REPLICATION_DISTRIBUTOR XML
INSERT INTO@REPLICATION_DISTRIBUTOR_TABLE (REPLICATION_DISTRIBUTOR)
SELECT
(
SELECT
X.name,
is_distributor
FROM
(
SELECTDISTINCT d.name
,d.is_published
,d.is_subscribed
,d.is_merge_published
,d.is_distributor
FROMsys.databases d
INNERJOIN SYS.master_files f
ON d.database_id = f.database_id
where 1 = 1
AND
d.state = 0 — online
)X
WHEREX.is_distributor =1
GROUPBY X.NAME,X.is_distributor
ORDERBY 1,2
FOR XML PATH(”)
)
SELECT @REPLICATION_DISTRIBUTOR = REPLICATION_DISTRIBUTOR FROM@REPLICATION_DISTRIBUTOR_TABLE
/*CPU and Memory Status*/
Declare @PhysicalMemory_MB bigint,@PhysicalMemory_GB bigint,@virtualMemory_GB bigint
Declare @productversion nvarchar(20)
select @productversion =cast(SERVERPROPERTY(‘productversion’) as nvarchar(20))
DECLARE @Sockets_Physical_CPU_Count INT
DECLARE @Hyperthread_Ratio_Core INT
DECLARE @Logical_Processor_CPU_Count INT
DECLARE @sqlserver_start_time datetime
SELECT
@Sockets_Physical_CPU_Count = cpu_count/hyperthread_ratio — AS [Sockets/Physical_CPU_Count],
,@Hyperthread_Ratio_Core=hyperthread_ratio —AS [Hyperthread_Ratio/Core],
,@Logical_Processor_CPU_Count = cpu_count — AS [Logical Processor/ CPU Count],
,@sqlserver_start_time=sqlserver_start_time
FROM sys.dm_os_sys_info
–select @productversion — = cast(@productversion as varchar(20))
if left(@productversion,2) = ’11’
begin
SELECT
@PhysicalMemory_MB=CEILING(physical_memory_kb/1024.0) — as [Physical Memory_MB],
,@PhysicalMemory_GB = CEILING(physical_memory_kb/1024/1024) –as [Physical Memory_GB],
,@virtualMemory_GB=CEILING(virtual_memory_kb/1024/1024) –as [Virtual Memory GB]
FROMsys.dm_os_sys_info
end
/*
if left(@productversion,2) = ’10’
begin
SELECT
@PhysicalMemory_MB=CEILING(physical_memory_in_bytes/1024.0) — as [Physical Memory_MB],
,@PhysicalMemory_GB = CEILING(physical_memory_in_bytes/1024/1024) –as [Physical Memory_GB],
,@virtualMemory_GB=CEILING(virtual_memory_in_bytes/1024/1024) –as [Virtual Memory GB]
FROM sys.dm_os_sys_info
end
*/
DECLARE @min_SQLServer_memory sql_variant
SELECT @min_SQLServer_memory=value
FROM sys.configurations
WHERE name like ‘%min server memory (MB)%’
DECLARE @max_SQLServer_memory sql_variant
SELECT @max_SQLServer_memory=value
FROM sys.configurations
WHERE name like ‘%max server memory (MB)%’
DECLARE @min_memory_per_query_kb sql_variant
SELECT @min_memory_per_query_kb = value
FROM sys.configurations
WHERE name like ‘%min memory per query (KB)%’
Declare @SQLServerAuthentication varchar(40)
SELECT
@SQLServerAuthentication =
CASE SERVERPROPERTY(‘IsIntegratedSecurityOnly’)
WHEN1 THEN ‘Windows Authentication’
WHEN0 THEN ‘Windows and SQL Server Authentication’
END
SELECT top 1
@@SERVICENAME AS INSTANCE,
SERVERPROPERTY(‘servername’) as ServerName,
SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as ComputerName,
SERVERPROPERTY(‘productversion’) as ProductVersion,
SERVERPROPERTY(‘productlevel’) as [Prod.Level],
SERVERPROPERTY(‘edition’) as Edition,
SERVERPROPERTY(‘IsClustered’) as IsClustered,
SERVERPROPERTY(‘SqlCharSet’) as SqlCharSet,
SERVERPROPERTY(‘SqlCharSetName’) as SqlCharSetName,
SERVERPROPERTY(‘SqlSortOrder’) as SqlSortOrder,
SERVERPROPERTY(‘SqlSortOrderName’) as SqlSortOrderName,
SERVERPROPERTY(‘collation’) AS SQLServerCollation,
CONNECTIONPROPERTY(‘net_transport’) AS net_transport,
CONNECTIONPROPERTY(‘protocol_type’) AS protocol_type,
CONNECTIONPROPERTY(‘auth_scheme’) AS auth_scheme,
CONNECTIONPROPERTY(‘local_net_address’) AS local_net_address,
CONNECTIONPROPERTY(‘local_tcp_port’) AS local_tcp_port,
–CONNECTIONPROPERTY(‘client_net_address’) AS client_net_address,
@SQLServerAuthentication asSQLServerAuthentication,
@Sockets_Physical_CPU_Count AS [Sockets/Physical_CPU_Count],
@Hyperthread_Ratio_Core AS[Hyperthread_Ratio/Core],
@Logical_Processor_CPU_Count AS [Logical Processor/ CPU Count],
@sqlserver_start_time AS sqlserver_start_time,
@PhysicalMemory_MB as [Physical Memory_MB],
@PhysicalMemory_GB as [Physical Memory_GB],
–@virtualMemory_GB as [Virtual Memory GB],
@min_SQLServer_memory as min_SQLServer_memory_MB,
@max_SQLServer_memory asmax_SQLServer_memory_MB,
@min_memory_per_query_kb asmin_memory_per_query_kb,
COALESCE(@MIRRORING_STATUS,‘No Mirroring’) ASMIRROING_STATUS,
COALESCE(@REPLICATION_PUBLISHER,‘No Publisher’) ASREPLICATION_PUBLISHER,
COALESCE(@REPLICATION_SUBSCRIBER,‘No Subscriber’) AS REPLICATION_SUBSCRIBER,
COALESCE(@REPLICATION_MERGE_PUBLISHER,‘No Merge Publisher’) AS REPLICATION_MERGE_PUBLISHER,
COALESCE(@REPLICATION_DISTRIBUTOR,‘No Distributor’) AS REPLICATION_DISTRIBUTOR,
COALESCE(@LOGSHIPPING_PRIMARYSECONDARY,‘No Logshipping’) AS LOGSHIPPING_PRIMARYSECONDARY,
SERVERPROPERTY (‘IsHadrEnabled’) as AlwaysOnEnable,
COALESCE(@ALWAYSON_STATUS,‘No AlwaysOn’) AS AlwaysOnInfo,
@virtual_machine_type_desc as virtual_machine_type_desc,@Server_type as Server_type,
OSVersion =RIGHT(@@version, LEN(@@version)- 3 –charindex (‘ ON ‘,@@VERSION))
/*
IF SERVERPROPERTY (‘IsHadrEnabled’) = 1
BEGIN
SELECT
AGC.name — Availability Group
, RCS.replica_server_name — SQL cluster node name
, ARS.role_desc — Replica Role
, AGL.dns_name — Listener Name
FROM
sys.availability_groups_cluster AS AGC
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
ON
RCS.group_id = AGC.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
ON
ARS.replica_id = RCS.replica_id
INNER JOIN sys.availability_group_listeners AS AGL
ON
AGL.group_id = ARS.group_id
WHERE
ARS.role_desc = ‘PRIMARY’
END
*/
/*
DECLARE @MirroringRole int;
SET @MirroringRole = (SELECT mirroring_role
FROM sys.database_mirroring
WHERE DB_NAME(database_id) = N’DB_X’); — your database name here
IF @MirroringRole = 2 — Mirror
— connect to the failover partner server, using your database
ELSE IF @MirroringRole = 1 — Principal
— connect to this server
END IF
USE MASTER
GO
–===========================================================
— before detaching the database
— see what files it has and where they are located
— checks mirror and replication
— database must not be part of mirror (neither as principal nor mirror) in order to be detached
–===========================================================
*/
set nocount on;
–To find database information:
–Declare @SQLServerAuthentication varchar(40)
SELECT
@SQLServerAuthentication =
CASE SERVERPROPERTY(‘IsIntegratedSecurityOnly’)
WHEN1 THEN ‘Windows Authentication’
WHEN0 THEN ‘Windows and SQL Server Authentication’
END
IF OBJECT_ID(‘tempdb..#temp’) IS NOT NULL
DROPTABLE #temp
CreateTable #temp
(
Database_ID INT,
DatabaseName sysname,
Name sysname,
physical_name nvarchar(500),
size_MB numeric (18,2),
FreeSpace_MB numeric (18,2)–,
–DBStatus INT,
–is_read_only BIT
)
Exec sp_msforeachdb ‘
Use [?];
Insert Into #temp (Database_ID,DatabaseName, Name, physical_name, Size_MB, FreeSpace_MB)
Select DB_ID(DB_NAME()) AS Database_ID,DB_NAME() AS [DatabaseName], Name, physical_name ,
Cast(Cast(Round(cast(size as int) * 8.0/1024.0,2) as int) as nvarchar) Size_MB ,
cast(Cast(Round(cast(size as int) * 8.0/1024.0,2) as int) –
Cast(FILEPROPERTY(name, ”SpaceUsed”) * 8.0/1024.0 as int) as nvarchar) As FreeSpace_MB
FROM
sys.database_files
‘
Select
T.DatabaseName,
@@SERVICENAME AS INSTANCE,
SERVERPROPERTY(‘servername’) as ServerName,
” AS Service,
SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as ComputerName,
SERVERPROPERTY(‘productversion’) as ProductVersion,
DATABASEPROPERTYEX(DatabaseName, ‘Status’) AS DBStatus,
Read_Write_Status =
case when d.is_read_only = 0 then ‘Read/Write’
else ‘Read’
End,
T.Name as[FileName],T.physical_name,
T.size_MB ASActual_Size_MB,
T.FreeSpace_MB,
@SQLServerAuthentication asSQLServerAuthentication
From#temp T
inner join sys.databases D
on T.Database_ID = D.Database_ID
where T.Database_ID not in (1,2,3,4)
set nocount off;
IntelliDB 