Skip Navigation LinksDiscussions List : Details info about your SQL Instance

Started: 9/11/2019 6:55 AM
Picture Placeholder: Zahid Ahamed
Picture Placeholder: Zahid Ahamed
  • Zahid Ahamed

### Details info about your SQL Instance

DECLARE @SERVERINFO_BASIC_TSQL VARCHAR(8000)

DECLARE @OSSTATS_SQL2000_TSQL VARCHAR(8000)

DECLARE @SERVERINFO_SQL2000_TSQL VARCHAR(8000)

DECLARE @SERVERINFO_SQL2005_TSQL VARCHAR(8000)

DECLARE @SERVERINFO_TSQL VARCHAR(8000)

 

 

SET @SERVERINFO_BASIC_TSQL = '

SELECT

CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY(''MachineName''))) AS [ServerName],

CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY(''InstanceName''))) AS [InstanceName],

CONVERT(VARCHAR(50),(SELECT

CASE (SELECT LEFT(CAST(SERVERPROPERTY(''ProductVersion'') AS VARCHAR), 4))

WHEN ''13.0'' THEN ''SQL Server 2016''

 

WHEN ''12.0'' THEN ''SQL Server 2014''

WHEN ''11.0'' THEN ''SQL Server 2012''

WHEN ''10.5'' THEN ''SQL Server 2008 R2''

WHEN ''10.0'' THEN ''SQL Server 2008''

WHEN ''9.00'' THEN ''SQL Server 2005''

WHEN ''8.00'' THEN ''SQL Server 2000''

ELSE ''Unknown Version''

END

)

) AS [Version],

CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY(''ProductLevel''))) AS [Build],

CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY(''ProductVersion''))) AS [BuildNumber],

CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY(''Edition''))) AS [Edition],

CONVERT(VARCHAR(50),(SELECT

CASE (SELECT SERVERPROPERTY(''IsIntegratedSecurityOnly''))

WHEN 1 THEN ''Windows''

WHEN 0 THEN ''Mixed Mode''

END

)

) AS [Authentication],

CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY(''Collation''))) AS [Collation],

GETDATE() AS [Timestamp],

'

SET @OSSTATS_SQL2000_TSQL = '

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

DROP TABLE ##OSstats

CREATE TABLE ##OSstats ([Index] VARCHAR(2000), [Name] VARCHAR(2000), [Internal_Value] VARCHAR(2000), [Character_Value] VARCHAR(2000))

INSERT INTO ##OSstats EXEC xp_msver'

 

 

SET @SERVERINFO_SQL2000_TSQL = '

CONVERT(BIGINT,(SELECT [value] FROM [master].[dbo].[sysconfigures] WHERE [config] = ''1544'')) AS [MemoryAllocatedMB],

CONVERT(INT, (SELECT [value] FROM [master].[dbo].[sysconfigures] WHERE [config] = ''1539'')) AS [MaxDOP],

CONVERT(INT, (SELECT [value] FROM [master].[dbo].[sysconfigures] WHERE [config] = ''1538'')) AS [CTFP],

(SELECT [Internal_Value] FROM ##OSstats WHERE [name] = ''ProcessorCount'') AS [Cores],

(SELECT [Internal_Value] FROM ##OSstats WHERE [name] = ''PhysicalMemory'') AS [TotalMemoryMB],

(SELECT [crdate] FROM [master].[dbo].[sysdatabases] WHERE [name] = ''tempdb'') AS [StartupTime]'

 

 

SET @SERVERINFO_SQL2005_TSQL = '

CONVERT(BIGINT,(SELECT [value] FROM [master].[dbo].[sysconfigures] WHERE [config] = ''1544'')) AS [MemoryAllocatedMB],

CONVERT(INT, (SELECT [value] FROM [master].[dbo].[sysconfigures] WHERE [config] = ''1539'')) AS [MaxDOP],

CONVERT(INT, (SELECT [value] FROM [master].[dbo].[sysconfigures] WHERE [config] = ''1538'')) AS [CTFP],

(SELECT [cpu_count] FROM [master].[sys].[dm_os_sys_info]) AS [Cores],

(SELECT [physical_memory_in_bytes]/1024/1024 FROM [master].[sys].[dm_os_sys_info]) AS [TotalMemoryMB],

(SELECT [create_date] FROM [master].[sys].[databases] WHERE [name] = ''tempdb'') AS [StartupTime]'

 

 

SET @SERVERINFO_TSQL = '

CONVERT(BIGINT,(SELECT [value] FROM [master].[sys].[configurations] WHERE [configuration_id] = ''1544'')) AS [MemoryAllocatedMB],

CONVERT(INT,( SELECT [value] FROM [master].[sys].[configurations] WHERE [configuration_id] = ''1539'')) AS [MaxDOP],

CONVERT(INT,( SELECT [value] FROM [master].[sys].[configurations] WHERE [configuration_id] = ''1538'')) AS [CTFP],

(SELECT [cpu_count] FROM [master].[sys].[dm_os_sys_info]) AS [Cores],

(SELECT [total_physical_memory_kb]/1024 FROM [master].[sys].[dm_os_sys_memory]) AS [TotalMemoryMB],

(SELECT [sqlserver_start_time] FROM [master].[sys].[dm_os_sys_info]) AS [StartupTime]'

 

 

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),1)='8'

BEGIN

EXEC (@OSSTATS_SQL2000_TSQL)

EXEC (@SERVERINFO_BASIC_TSQL + @SERVERINFO_SQL2000_TSQL)

END

ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),1)='9'

BEGIN

EXEC (@SERVERINFO_BASIC_TSQL + @SERVERINFO_SQL2005_TSQL)

END

ELSE

BEGIN

EXEC (@SERVERINFO_BASIC_TSQL + @SERVERINFO_TSQL)

END

Picture Placeholder: Zahid Ahamed
  • Zahid Ahamed
/_layouts/15/images/person.gif" alt="Picture Placeholder: Zahid Ahamed" />
Zahid Ahamed

DECLARE @SERVERINFO_BASIC_TSQL VARCHAR(8000)

DECLARE @OSSTATS_SQL2000_TSQL VARCHAR(8000)

DECLARE @SERVERINFO_SQL2000_TSQL VARCHAR(8000)

DECLARE @SERVERINFO_SQL2005_TSQL VARCHAR(8000)

DECLARE @SERVERINFO_TSQL VARCHAR(8000)

 

 

SET @SERVERINFO_BASIC_TSQL = '

SELECT

CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY(''MachineName''))) AS [ServerName],

CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY(''InstanceName''))) AS [InstanceName],

CONVERT(VARCHAR(50),(SELECT

CASE (SELECT LEFT(CAST(SERVERPROPERTY(''ProductVersion'') AS VARCHAR), 4))

WHEN ''13.0'' THEN ''SQL Server 2016''

 

WHEN ''12.0'' THEN ''SQL Server 2014''

WHEN ''11.0'' THEN ''SQL Server 2012''

WHEN ''10.5'' THEN ''SQL Server 2008 R2''

WHEN ''10.0'' THEN ''SQL Server 2008''

WHEN ''9.00'' THEN ''SQL Server 2005''

WHEN ''8.00'' THEN ''SQL Server 2000''

ELSE ''Unknown Version''

END

)

) AS [Version],

CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY(''ProductLevel''))) AS [Build],

CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY(''ProductVersion''))) AS [BuildNumber],

CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY(''Edition''))) AS [Edition],

CONVERT(VARCHAR(50),(SELECT

CASE (SELECT SERVERPROPERTY(''IsIntegratedSecurityOnly''))

WHEN 1 THEN ''Windows''

WHEN 0 THEN ''Mixed Mode''

END

)

) AS [Authentication],

CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY(''Collation''))) AS [Collation],

GETDATE() AS [Timestamp],

'

SET @OSSTATS_SQL2000_TSQL = '

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

DROP TABLE ##OSstats

CREATE TABLE ##OSstats ([Index] VARCHAR(2000), [Name] VARCHAR(2000), [Internal_Value] VARCHAR(2000), [Character_Value] VARCHAR(2000))

INSERT INTO ##OSstats EXEC xp_msver'

 

 

SET @SERVERINFO_SQL2000_TSQL = '

CONVERT(BIGINT,(SELECT [value] FROM [master].[dbo].[sysconfigures] WHERE [config] = ''1544'')) AS [MemoryAllocatedMB],

CONVERT(INT, (SELECT [value] FROM [master].[dbo].[sysconfigures] WHERE [config] = ''1539'')) AS [MaxDOP],

CONVERT(INT, (SELECT [value] FROM [master].[dbo].[sysconfigures] WHERE [config] = ''1538'')) AS [CTFP],

(SELECT [Internal_Value] FROM ##OSstats WHERE [name] = ''ProcessorCount'') AS [Cores],

(SELECT [Internal_Value] FROM ##OSstats WHERE [name] = ''PhysicalMemory'') AS [TotalMemoryMB],

(SELECT [crdate] FROM [master].[dbo].[sysdatabases] WHERE [name] = ''tempdb'') AS [StartupTime]'

 

 

SET @SERVERINFO_SQL2005_TSQL = '

CONVERT(BIGINT,(SELECT [value] FROM [master].[dbo].[sysconfigures] WHERE [config] = ''1544'')) AS [MemoryAllocatedMB],

CONVERT(INT, (SELECT [value] FROM [master].[dbo].[sysconfigures] WHERE [config] = ''1539'')) AS [MaxDOP],

CONVERT(INT, (SELECT [value] FROM [master].[dbo].[sysconfigures] WHERE [config] = ''1538'')) AS [CTFP],

(SELECT [cpu_count] FROM [master].[sys].[dm_os_sys_info]) AS [Cores],

(SELECT [physical_memory_in_bytes]/1024/1024 FROM [master].[sys].[dm_os_sys_info]) AS [TotalMemoryMB],

(SELECT [create_date] FROM [master].[sys].[databases] WHERE [name] = ''tempdb'') AS [StartupTime]'

 

 

SET @SERVERINFO_TSQL = '

CONVERT(BIGINT,(SELECT [value] FROM [master].[sys].[configurations] WHERE [configuration_id] = ''1544'')) AS [MemoryAllocatedMB],

CONVERT(INT,( SELECT [value] FROM [master].[sys].[configurations] WHERE [configuration_id] = ''1539'')) AS [MaxDOP],

CONVERT(INT,( SELECT [value] FROM [master].[sys].[configurations] WHERE [configuration_id] = ''1538'')) AS [CTFP],

(SELECT [cpu_count] FROM [master].[sys].[dm_os_sys_info]) AS [Cores],

(SELECT [total_physical_memory_kb]/1024 FROM [master].[sys].[dm_os_sys_memory]) AS [TotalMemoryMB],

(SELECT [sqlserver_start_time] FROM [master].[sys].[dm_os_sys_info]) AS [StartupTime]'

 

 

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),1)='8'

BEGIN

EXEC (@OSSTATS_SQL2000_TSQL)

EXEC (@SERVERINFO_BASIC_TSQL + @SERVERINFO_SQL2000_TSQL)

END

ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),1)='9'

BEGIN

EXEC (@SERVERINFO_BASIC_TSQL + @SERVERINFO_SQL2005_TSQL)

END

ELSE

BEGIN

EXEC (@SERVERINFO_BASIC_TSQL + @SERVERINFO_TSQL)

END

09/11/2019 6:55 AM9/11/2019 6:55 AMNoGeneral
0
8/18/2019 10:40 PM
There are no items to show in this view of the "Discussions List" discussion board.