Document the output of all the SPs in HTML fashion
A script to document the output columns of all the SPs in a given database, with a HTML formatted document
2015-09-24 (first published: 2015-09-08)
1,906 reads
-- Internal variables DECLARE @SQLNVarchar(4000) , @PhyMBNVarchar(100) , @BPool_ComNVarchar(100) , @BPool_TgtNVarchar(100) , @BPool_VisNVarchar(100) , @VMTypeNVarchar(100) -- DMVs column names and availability can differ between versions. -- The differences can be taken into account here. -- Create other variables for other columns. IF SUBSTRING(@@VERSION, 22, 4) >= 2012 BEGIN SET @PhyMB= N'CAST(OSI.physical_memory_kb / 1024.0 / 1024.0 AS Dec(9,2))' SET @BPool_Com= N'CAST(committed_kb / 128.0 / 1024 AS Dec(9,2))' SET @BPool_Tgt= N'CAST(committed_target_kb / 128.0 / 1204 AS Dec(9,2))' SET @BPool_Vis= N'CAST(visible_target_kb / 128.0 /1024 AS Dec(9,2))' SET @VMType= N'virtual_machine_type_desc' END ELSE BEGIN SET @PhyMB= N'CAST(OSI.physical_memory_in_bytes / 1024.0 / 1024.0 / 1024.0 AS Dec(9,2))' SET @BPool_Com= N'CAST(bpool_committed / 128.0 / 1024 AS Dec(9,2))' SET @BPool_Tgt= N'CAST(bpool_commit_target / 128.0 / 1204 AS Dec(9,2))' SET @BPool_Vis= N'CAST(bpool_visible / 128.0 /1024 AS Dec(9,2))' SET @VMType= N'''column not supported in this version of SQL''' END -- Add the variables into the query SET @SQL = N';WITH Config AS ( SELECTMachineName= SERVERPROPERTY(''MachineName'') , InstanceName= SERVERPROPERTY(''ServerName'') , [Edition] = ''' + CASE WHEN SUBSTRING(@@VERSION, 27, 2) = 'R2' THEN SUBSTRING(@@VERSION, 22, 7) ELSE SUBSTRING(@@VERSION, 22, 4) END + ' '' + CAST(SERVERPROPERTY(''Edition'') AS Varchar(30)) , [Version]= SERVERPROPERTY(''ProductVersion'') , [Level]= SERVERPROPERTY(''ProductLevel'') , DBCnt= (SELECT COUNT(1) FROM sys.Databases) , TotalDBSizeGB= (SELECT CAST(SUM(Size) / 128.0 / 1024 AS Dec(9,2)) FROM sys.master_files) -- Ex filestream , Collation= SERVERPROPERTY(''Collation'') , IsFullTextInstalled= SERVERPROPERTY(''IsFullTextInstalled'') , IsHadrEnabled= SERVERPROPERTY(''IsHadrEnabled'') , MinRAMMB= MAX(CASE WHEN Name = ''min server memory (MB)'' THEN Value_in_use END) , MaxRAMMB= MAX(CASE WHEN Name = ''max server memory (MB)'' THEN Value_in_use END) , CostParallelism= MAX(CASE WHEN Name = ''cost threshold for parallelism'' THEN Value_in_use END) , DegreeParallelism= MAX(CASE WHEN Name = ''max degree of parallelism'' THEN Value_in_use END) , RemoteAdminConn= MAX(CASE WHEN Name = ''remote admin connections'' THEN Value_in_use END) , AllowXPCmdShell= MAX(CASE WHEN Name = ''xp_cmdshell'' THEN Value_in_use END) FROMsys.Configurations C --SELECT* FROMsys.Configurations C ) , OSI AS ( SELECTSvrName= @@SERVERNAME , LastSvrReboot= DATEADD(SECOND, -1 * (OSI.ms_ticks / 1000), GETDATE()) , SvrUptime= LEFT(CONVERT(Varchar(20), DATEADD(SECOND, (OSI.ms_ticks / 1000), 0), 114), LEN(CONVERT(Varchar(20), DATEADD(SECOND, (OSI.ms_ticks / 1000), 0), 114)) - 4) , LastSQLRestart= sqlserver_start_time , SQLUptime= LEFT(CONVERT(Varchar(20), DATEADD(SECOND, DATEDIFF(SECOND, sqlserver_start_time, GETDATE()), 0), 114), LEN(CONVERT(Varchar(20), DATEADD(SECOND, DATEDIFF(SECOND, sqlserver_start_time, GETDATE()), 0), 114)) - 4) , CPUCnt= OSI.cpu_count , VirtualCoresPerCPU= OSI.hyperthread_ratio , ServerRamGB= ' + @PhyMB + ' , BPoolCommittedGB= ' + @BPool_Com + ' , BPoolTargetGB= ' + @BPool_Tgt + ' , BPoolVisibleGB= ' + @BPool_Vis + ' , VirtualMachineType= ' + @VMType + ' FROMsys.dm_os_sys_info OSI ) SELECT* FROMConfig C JOIN OSI ON C.InstanceName = OSI.SvrName' -- Run the query EXEC sp_ExecuteSQL @SQL --PRINT @SQL