SQL Server Instance and DB Information - SQL 2005

SQL Server 2005:

set nocount on;

DECLARE @local_net_address varchar(48),@local_tcp_port int

SELECT top 1 @local_tcp_port = local_tcp_port, @local_net_address = local_net_address

    FROM sys.dm_exec_connections

    WHERE (local_tcp_port IS NOT NULL AND local_net_address  IS NOT NULL )

 

 

DECLARE @net_transport VARCHAR(8000)

SELECT @net_transport = COALESCE(@net_transport + ', ', '') + net_transport

 FROM sys.dm_exec_connections

WHERE net_transport IS NOT NULL

GROUP BY net_transport

--SELECT @net_transport

 

 

DECLARE @protocol_type VARCHAR(8000)

SELECT @protocol_type = COALESCE(@protocol_type + ', ', '') + protocol_type

 FROM sys.dm_exec_connections

WHERE protocol_type IS NOT NULL

GROUP BY protocol_type

--SELECT @protocol_type

 

 

DECLARE @auth_scheme VARCHAR(8000)

SELECT @auth_scheme = COALESCE(@auth_scheme + ', ', '') + auth_scheme

 FROM sys.dm_exec_connections

WHERE auth_scheme IS NOT NULL

GROUP BY auth_scheme

--SELECT @auth_scheme

 

 

/*Virtual Machine Check*/

 

DECLARE @result int

EXEC @result = xp_cmdshell 'SYSTEMINFO'

 

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

 

 

/* 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,

  case when LP.primary_database IS null then 'No' else 'Yes' end LSPConfigured,

  case when LS.secondary_database IS null then 'No' else 'Yes' end LSSConfigured

From

       master.dbo.sysdatabases SD

       left join msdb.dbo.log_shipping_monitor_primary LP on LP.primary_database=SD.name

       left join 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 @MIRROING_STATUS INT

 

SELECT @MIRROING_STATUS = COUNT(X.name)  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))

 FROM sys.databases d

      INNER JOIN sys.master_files f

                 ON d.database_id = f.database_id

 

      LEFT OUTER JOIN sys.database_mirroring m

                      ON d.database_id = m.database_id           

where 1 = 1

  AND d.state = 0 -- online

 )X

 WHERE X.DB_in_Mirror <> 'Not Part in Mirror'

 

 

/*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

(     

 SELECT DISTINCT d.name

      ,d.is_published

      ,d.is_subscribed

      ,d.is_merge_published

      ,d.is_distributor

      FROM sys.databases d

      INNER JOIN sys.master_files f

                 ON d.database_id = f.database_id

   

where 1 = 1

  AND

  d.state = 0 -- online

  )X

   WHERE X.is_published = 1

 GROUP BY X.name,X.is_published

  ORDER BY 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

(     

 SELECT DISTINCT d.name

      ,d.is_published

      ,d.is_subscribed

      ,d.is_merge_published

      ,d.is_distributor

      FROM sys.databases d

      INNER JOIN sys.master_files f

                 ON d.database_id = f.database_id

   

where 1 = 1

  AND

  d.state = 0 -- online

  )X

   WHERE X.is_subscribed = 1

 GROUP BY X.name,X.is_subscribed

  ORDER BY 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

(     

 SELECT DISTINCT d.name

      ,d.is_published

      ,d.is_subscribed

      ,d.is_merge_published

      ,d.is_distributor

      FROM sys.databases d

      INNER JOIN sys.master_files f

                 ON d.database_id = f.database_id

   

where 1 = 1

  AND

  d.state = 0 -- online

  )X

   WHERE X.is_merge_published = 1

 GROUP BY X.name,X.is_merge_published

  ORDER BY 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

(     

 SELECT DISTINCT d.name

      ,d.is_published

      ,d.is_subscribed

      ,d.is_merge_published

      ,d.is_distributor

      FROM sys.databases d

      INNER JOIN sys.master_files f

                 ON d.database_id = f.database_id

   

where 1 = 1

  AND

  d.state = 0 -- online

  )X

   WHERE X.is_distributor = 1

 GROUP BY X.name,X.is_distributor

  ORDER BY 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 @sqlserver_start_time = login_time FROM sysprocesses WHERE spid =1

 

/*

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]

 FROM sys.dm_os_sys_info

goto escapefromsql2008

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') 

 WHEN 1 THEN 'Windows Authentication'  

 WHEN 0 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,    

@net_transport AS net_transport, 

@protocol_type AS protocol_type, 

@auth_scheme AS auth_scheme,     

@local_net_address AS local_net_address,

@local_tcp_port AS local_tcp_port,

--CONNECTIONPROPERTY('client_net_address') AS client_net_address,

@SQLServerAuthentication as SQLServerAuthentication,

@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 as max_SQLServer_memory_MB,

@min_memory_per_query_kb as min_memory_per_query_kb,  

COALESCE(@MIRROING_STATUS,'No Mirroring') as MIRROING_STATUS,

COALESCE(@REPLICATION_PUBLISHER,'No Publisher')  AS REPLICATION_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,

COALESCE(SERVERPROPERTY ('IsHadrEnabled'),'0') as AlwaysOnEnable,

'No AlwaysOn' AS AlwaysOnInfo,

'Refer systeminfo' virtual_machine_type_desc,'Refer systeminfo'   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

 

 

Reference: http://dba.stackexchange.com/questions/36755/how-do-i-determine-if-a-database-is-the-principal-in-a-mirroring-setup

 

 

USE MASTER;

 

 

--===========================================================

-- 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

--===========================================================

 

*/

 

 

 

 

--To find database information:

 

   

IF OBJECT_ID('tempdb..#temp') IS NOT NULL

    DROP TABLE #temp

 

 

    Create Table #temp

(

    Database_ID INT,

    DatabaseName sysname,

    Name sysname,

    physical_name nvarchar(500),

    size_MB decimal (18,2),

    FreeSpace_MB decimal (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 decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size_MB,

    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -

        Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) 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 AS Actual_Size_MB,

T.FreeSpace_MB,

@SQLServerAuthentication as SQLServerAuthentication

 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;