MS SQL Server

SQL Server Instance and DB Information – SQL 2000

SQL Server Information

SQL Server 2000:

Many of the companies like to keep their database information as a report and that will be useful when it comes to upgrade or finding of the particular database or server information. This is very useful information which we believe that needs to be kept in the document folder. Anyone in the Infrastructure Team can use this useful information without searching through all of their databases instances.

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)


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



if left(@productversion,2) = '8' 

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,	
'' AS net_transport,	
'' AS protocol_type,	
'' AS auth_scheme,	
'' AS local_net_address,	
'' AS local_tcp_port,	
--CONNECTIONPROPERTY('client_net_address') AS client_net_address,
@SQLServerAuthentication as SQLServerAuthentication,
''  AS [Sockets/Physical_CPU_Count],
'' AS [Hyperthread_Ratio/Core],
'' AS [Logical Processor/ CPU Count], 
'' AS  sqlserver_start_time,
'' as [Physical Memory_MB], 
''  as [Physical Memory_GB], 
--@virtualMemory_GB as [Virtual Memory GB],
'' as min_SQLServer_memory_MB,
'' as max_SQLServer_memory_MB,
'' as min_memory_per_query_kb,	

'Refer Server' AS MIRROING_STATUS,
'Refer Server'   AS REPLICATION_PUBLISHER,
'Refer Server'  AS REPLICATION_SUBSCRIBER,
'Refer Server'  AS REPLICATION_MERGE_PUBLISHER,
'Refer Server'  AS REPLICATION_DISTRIBUTOR,
'Refer Server'  AS LOGSHIPPING_PRIMARYSECONDARY,
'Refer Server'  AlwaysOnEnable,


/*
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 smallint,
    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 SAF.dbid AS Database_ID,DB_NAME() AS [DatabaseName], SF.Name,  SF.filename as physical_name,
    Cast(Cast(Round(cast(SF.size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size_MB,
    Cast(Cast(Round(cast(SF.size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -
        Cast(FILEPROPERTY(SF.name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace_MB
    FROM
dbo.sysfiles SF inner join master.dbo.sysaltfiles SAF 
on SF.fileid = SAF.fileid and SF.name COLLATE DATABASE_DEFAULT = SAF.name COLLATE DATABASE_DEFAULT

    
'
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,
DATABASEPROPERTYEX(DatabaseName, 'Updateability') AS Read_Write_Status,
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 master.dbo.sysdatabases D
on T.Database_ID = D.dbid
where T.Database_ID not in (1,2,3,4)


set nocount off;
go



 

 

 
–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

    DROPTABLE #temp

 

 

    CreateTable #temp

(

    Database_ID smallint,

    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 SAF.dbid AS Database_ID,DB_NAME() AS [DatabaseName], SF.Name,  SF.filename as physical_name,

    Cast(Cast(Round(cast(SF.size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size_MB,

    Cast(Cast(Round(cast(SF.size as decimal) * 8.0/1024.0,2) as decimal(18,2)) –

        Cast(FILEPROPERTY(SF.name, ”SpaceUsed”) * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace_MB

    FROM

dbo.sysfiles SF inner join master.dbo.sysaltfiles SAF 

on SF.fileid = SAF.fileid and SF.name COLLATE DATABASE_DEFAULT = SAF.name COLLATE DATABASE_DEFAULT

 

    

‘

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,

DATABASEPROPERTYEX(DatabaseName, ‘Updateability’) AS Read_Write_Status,

T.Name as[FileName],T.physical_name,

T.size_MB ASActual_Size_MB,

T.FreeSpace_MB,

@SQLServerAuthentication  as SQLServerAuthentication

 From#temp T

inner join master.dbo.sysdatabases D

on T.Database_ID = D.dbid

where T.Database_ID not in (1,2,3,4)

set nocount off;

go

 

 
 

Leave a Reply

Your email address will not be published. Required fields are marked *