SQL Version - Let's Go Further Beyond @@version

When DBAs need to check SQL Server edition, version, build or patch level, most of them use @@version to get the info. Too often I found it's insufficient. Let me give you a few examples:

1. @@version does not provide info on cumulative update or other special security patches. For instance, on a system with build no 12.0.5557.0, it only shows SQL Server 2014 SP2, but more accurately it's SP2 cumulative update package 8 (CU8). The CU details sometimes are critical to compare between SQL instances in order to bring them to consistency or t-shoot a problem due to inconsistency.

2. SQL version closely links to Windows version. So it's a common desire to not only find info at SQL Server level but also at an OS level. Before SQL Server 2016 the OS info is vague to say the least. Let's say, on a Windows Server 2012 system, you might find @@version returns "Windows NT 6.2 <x64> (Build 9200)". I hear ya, we all hate that! Fortunately beginning on SQL 2016, they put more welcome message like "Windows Server 2016 Datacenter Evaluation 10.0 <X64> (Build 14393: ) (Hypervisor)" - much better, huh?

3. The built-in function @@version lacks visibility of tracking down the source of a patch, which usually is a Microsoft Knowledge Base article. It also lacks a concise yet detailed enough description of what a patch is targeted for.  For example, build no 13.0.4199 is an important update for SQL Server 2016 SP1 Reporting Service. If you search for the build number you'll find great details from KB3207512.

4. The official release dates. Don't get confused by the build date returned by @@version. The release dates are more important to end users as they can have a broad view of the life cycles of the products they purchase. The dates also help SQL guys determine how far their servers fall behind of current level.

5. An indicator of hardware model, whether a physical server, a virtual machine, or a Hyper-V, is definitely welcome. In addition, any critical info such as SQL Server's processors, memory, min/max target server memory, etc makes a DBA's life easier.

All the above mentioned shortcomings can be fixed when you use this blog post:

http://sqlserverbuilds.blogspot.com/

For the past decade it has been my de facto web site for SQL Server version checking. Just to disclaim that I have no affiliation with the blogger. I have no knowledge who created and is maintaining this site. Whoever did it it's a job well done. It is probably the most complete and detailed list of all SQL Server patches you can find on the web. It covers the release of version 7.0 all the way up to SQL 2017.

How nice would it be if we could combine the merits from this web site with the good stuff returned by @@version? Well, that's why I'm blogging in this post to show you my 90% automated solution. Fortunately the 10% manual work is as easy as a breeze. Here we go.

My testing prerequisites:
- Any SQL versions between 2005 and 2017, with xp_cmdshell enabled
- Excel 2013/2016
- SQL Server Data Tool (Visual Studio 2015)

Step 1.   At your own leisure copy version contents from the blog site into an Excel spreadsheet. To do so first find the beginning line of your interest, such as "Microsoft SQL Server 2017 Builds". Hold down the shift key while highlighting sections. You can scroll the right bar up and down to quickly get to desired spot, e.g. the end of chart for "Microsoft SQL Server 2000 Builds". Copy and paste it to an blank Excel workbook. Save it as "CSV (Comma delimited) file. When prompted if some special info in the Excel needs to be saved, click yes. The file location is C:\Temp\SQLServerBuild.

Step 2.   In the same folder mentioned in Step 1, create two .sql files for header and tail. They will be merged with a body text to form an intact stored procedure.

Header T-SQL:

USE master;
 GO

IF OBJECT_ID ( 'dbo.sp_SQLServerBuildInfo', 'P' ) IS NOT NULL
   BEGIN
     DROP PROCEDURE dbo.sp_SQLServerBuildInfo;
     IF OBJECT_ID('dbo.sp_SQLServerBuildInfo', 'P' ) IS NOT NULL
         PRINT '*** FAILED DROPPING PROCEDURE dbo.sp_SQLServerBuildInfo ***';
     ELSE
         PRINT '*** DROPPED PROCEDURE dbo.sp_SQLServerBuildInfo ****';
   END;
 GO

CREATE PROCEDURE dbo.sp_SQLServerBuildInfo 
AS

-- *********************************************************************************
 -- Procedure Name:  sp_SQLServerBuildInfo
 -- Purpose:         List SQL Server build no, etc for SQL Server 2005 -> 2017
 -- Note:            Refer to this site: http://sqlserverbuilds.blogspot.com/
 -- Author:          Richard Ding
 -- Date Created:    01/28/2018
 -- Date Modified:   
 --**********************************************************************************

 SET NOCOUNT ON;

 IF EXISTS (SELECT * FROM sys.configurations where name = 'xp_cmdshell' and value_in_use = 1)
   BEGIN
     if object_id('tempdb.dbo.#SystemInfo', 'U') is not null
   drop table #SystemInfo
     create table #SystemInfo (ItemValue VARCHAR(5000));
     INSERT INTO #SystemInfo EXEC ('xp_cmdshell ''systeminfo /FO LIST''');
   END
 ELSE
BEGIN
RAISERROR ('Stored procedure sp_SQLServerBuildInfo requries xp_cmdshell to be turn on. Please check.', 16, 1)
RETURN (0);
END;

DECLARE @OSName varchar(100), @OSVersion varchar(100), @SystemManufacturer varchar(60), @SystemModel varchar(60), @Model varchar(100);
 SELECT @OSName = LTRIM(RTRIM(SUBSTRING(ItemValue, CHARINDEX(':', ItemValue)+1, LEN(ItemValue) - CHARINDEX(':', ItemValue))))
                  FROM #SystemInfo WHERE ItemValue LIKE 'OS Name%';
 SELECT @OSVersion = LTRIM(RTRIM(SUBSTRING(ItemValue, CHARINDEX(':', ItemValue)+1, LEN(ItemValue) - CHARINDEX(':', ItemValue))))
                     FROM #SystemInfo WHERE ItemValue LIKE 'OS Version%';
 SELECT @SystemManufacturer = ItemValue FROM #SystemInfo WHERE ItemValue LIKE 'System Manufacturer%';
 SELECT @SystemModel = ItemValue FROM #SystemInfo WHERE ItemValue LIKE 'System Model%';
 SELECT @Model = '(' + LTRIM(RTRIM(SUBSTRING(@SystemManufacturer, CHARINDEX(':', @SystemManufacturer)+1, LEN(@SystemManufacturer) - CHARINDEX(':',
                 @SystemManufacturer)))) + ') ' + LTRIM(RTRIM(SUBSTRING(@SystemModel, CHARINDEX(':', @SystemModel)+1, LEN(@SystemModel) - CHARINDEX(':', @SystemModel))));


IF object_id('tempdb.dbo.#versioninfo', 'U') IS NOT NULL
DROP TABLE #versioninfo
CREATE TABLE #versioninfo ([Index] varchar(5), [Name] varchar(20), Internal_Value varchar(10), Character_Value varchar(120));   
INSERT INTO #versioninfo EXEC ('master.dbo.xp_msver');
   

DECLARE @ProductVersion varchar(50), @cpuspeedcount varchar(3), @Memory varchar(5);
 SELECT @ProductVersion = CONVERT(VARCHAR(50), SERVERPROPERTY('ProductVersion'));
 SET @cpuspeedcount = (SELECT Internal_Value FROM #versioninfo WHERE Name = 'ProcessorCount');
 SET @Memory = (SELECT Internal_Value FROM #versioninfo WHERE Name = 'PhysicalMemory');

 DECLARE @cpuidentifier varchar(100), @CPUNameString varchar(100);

/*  Find CPU Name String such as "Intel(R) Xeon(TM) CPU 1.60GHz"  */
 EXEC master.dbo.xp_regread
   @rootkey='HKEY_LOCAL_MACHINE',
   @key='HARDWARE\DESCRIPTION\system\Centralprocessor\0',
   @value_name='ProcessorNameString',
   @value = @CPUNameString OUTPUT,
   @no_output = 'no_output';

DECLARE @MaxMemory varchar(5), @MinMemory varchar(5)
SELECT @MaxMemory = convert(varchar(5), cast(value_in_use as int)/1024)  FROM sys.configurations where name = 'max server memory (MB)'
SELECT @MinMemory = convert(varchar(5), cast(value_in_use as int)/1024)  FROM sys.configurations where name = 'min server memory (MB)'

--  Big select statement:
 SELECT @OSName as 'Windows OS Edition'
 , @OSVersion AS 'Windows OS Service pack'
 , @Model AS 'Hardware Model'
 , @Memory + ' MB (SQL Min:' + @MinMemory + ' GB - Max:' + @MaxMemory + ' GB)' AS 'Memory'
 , REPLACE(REPLACE(REPLACE(left(LTRIM(@CPUNameString), 50), '    ', ' '), '   ', ' '), '  ', '') +' (x' + @cpuspeedcount + ') ' AS 'Processor'
 , CONVERT(VARCHAR(50), SERVERPROPERTY('ServerName')) AS 'SQL Server Name'
 , CASE
     WHEN @ProductVersion LIKE '8.0%' THEN 'SQL Server 2000'
     WHEN @ProductVersion LIKE '9.0%' THEN 'SQL Server 2005'
     WHEN @ProductVersion LIKE '10.0%' THEN 'SQL Server 2008'
     WHEN @ProductVersion LIKE '10.50%' THEN 'SQL Server 2008 R2'
     WHEN @ProductVersion LIKE '11.0%' THEN 'SQL Server 2012'
     WHEN @ProductVersion LIKE '12.0%' THEN 'SQL Server 2014'    
  WHEN @ProductVersion LIKE '13.0%' THEN 'SQL Server 2016'
WHEN @ProductVersion LIKE '14.0%' THEN 'SQL Server 2017'
   ELSE '' END + ' ' + CONVERT(VARCHAR(50), SERVERPROPERTY('Edition')) as 'SQL Server Edition', 
   CASE
   --  End of header

Tail T-SQL:

--  Begining of tail:
   ELSE '<' + @ProductVersion + '> Unknown' END AS 'SQL Server Build & Product Version'
--  End of tail 

RETURN 0;   
GO

GRANT EXEC ON sp_SQLServerBuildInfo TO public;
GO

Step 3.   Create an Integration Service solution/project in SQL Server Data Tool. Items are described in the order they are created.

(1)  SQL Server Connection Manager Data Source "(local)". Use Windows Authentication to login in local default instance. Select tempdb as target environment.

(2) Flat File Connection Manager Data Source "SQLServerBuilds.csv". Each detailed windows are shown below.

(3) Execute SQL Control Flow Task "Prep temp table". All settings are default. The T-SQL statement is below

USE [tempdb];

if object_id ('tempdb.dbo.SQLServerBuilds', 'U') is not null
drop table [SQLServerBuilds];

CREATE TABLE [dbo].[SQLServerBuilds](
--ID int identity primary key clustered,
[Build] [nvarchar](500) NULL,
[File version] [nvarchar](500) NULL,
[KB / Description] [nvarchar](4000) NULL,
[Release Date] [nvarchar](500) NULL
);

(4) Data Flow Task "Import from CSV". Note not all columns are selected. 

(5) Execute SQL Task Control Flow "Cleaning garbage". Its DML commands are in the following:

UPDATE tempdb.dbo.[SQLServerBuilds] 

SET [KB / Description] = CONCAT([KB / Description], ', ', substring([Release Date], 1, charindex('",', [Release Date])))

WHERE [Release Date] LIKE '%",%'

UPDATE tempdb.dbo.[SQLServerBuilds] 

SET [Release Date] = STUFF ([Release Date], 1, charindex('",', [Release Date]) + 1, '')

WHERE [Release Date] LIKE '%",%'

DELETE FROM tempdb.dbo.[SQLServerBuilds] WHERE Build IS NULL OR LEFT (Build, 1) LIKE '%[A-Z]%'

UPDATE tempdb.dbo.[SQLServerBuilds] 

SET [KB / Description] = STUFF ([KB / Description], 1, 1, '')

WHERE [KB / Description] LIKE '"%'    --  181 rows affected

UPDATE tempdb.dbo.[SQLServerBuilds] 

SET [KB / Description] = STUFF ([KB / Description], 1, 1, '')

WHERE [KB / Description] LIKE '"%'    --  181 rows affected

UPDATE tempdb.dbo.[SQLServerBuilds] 

SET [KB / Description] = STUFF ([KB / Description], LEN([KB / Description]), 1, '')

WHERE [KB / Description] LIKE '%"'    --  181 rows affected

UPDATE tempdb.dbo.[SQLServerBuilds] SET [Release Date] = REPLACE(REPLACE ([release date], '*new"', ''), '"', '')

UPDATE tempdb.dbo.[SQLServerBuilds] SET Build = REPLACE(Build, '.00.', '.0.') where Build like '1[0-2].00.%'    --  251 rows 

(6) Execute SQL Control Flow Task "Create sp_SQLServerBuildInfo". Its DML commands are in the following:

exec master.dbo.xp_cmdshell 'sqlcmd -E -i "C:\Temp\SQLServerBuild\sp_SQLServerBuild.sql"'

(7) Execute SQL Control Flow Task "Combine into sproc". Its DML commands are in the following:

exec master.dbo.xp_cmdshell 'copy C:\Temp\SQLServerBuild\*.sql C:\Temp\SQLServerBuild\sp_SQLServerBuildInfo.sql /y'

(8) Execute SQL Control Flow Task "Generate body syntax". Its DML commands are in the following:

declare @string varchar (8000)

select @string = 'set nocount on select ''WHEN @ProductVersion LIKE '''''' + Build + ''%'''' THEN ''''<'''' + @ProductVersion + ''''> '' + replace([KB / Description], '''''''', '''''''''''') +  '' ('' + ISNULL([Release Date], '''') +  '')'''''' from tempdb.dbo.[SQLServerBuilds] order by [File version] asc'

select @string = 'sqlcmd -E -d tempdb -Q "' + @string + '" -o "C:\Temp\SQLServerBuild\2. body.sql"'

print @string

exec master.dbo.xp_cmdshell @string

(9) Execute the whole package (may need to execute the first control flow task separately to resolve object reference). Check the result file at C:\Temp\SQLServerBuild\sp_SQLServerBuildInfo.sql. The body part contains about 1000 lines of code so I won't list it here. Below i compared the results of sp_SQLServerBuildInfo and @@version just to see the fine differences.

sp_SDS: The Saga continues...

First of all, let me say an apology for not following up on TechTarget since I published sp_SDS in May, 2008, despite of pretty good feedback in the SQL Server community. I've been using sp_SDS during the last decade with occasional modifications, most of which are SQL build eligibility. If you're interested please check my original post here:
http://searchsqlserver.techtarget.com/tip/Check-SQL-Server-database-and-log-file-size-with-this-stored-procedure

In this post I'd like to present the current code of stored procedure sp_SDS. I will not repeat what was described in the TechTarget article but instead I'm putting down some notes on improvements. As database sizes increase to terabytes, the original code broke due to insufficient decimal conversion. In this most updated version decimal numbers are expanded to scale of 16, enough to accommodate petabytes which is rarely seen nowadays. One pain of updating the code is to keep up with new releases of SQL Servers. In this version of sp_SDS, I made it current to work with SQL Server 2017. In addition I boldly put down the next three future versions - SQL Server 2018/2019/2020. Given the crazy pace that Microsoft releases its products, it might very well be a good guess. Lastly, I added a column to show database recovery mode since many times I found I need that information handy.

As usual, feel free to use it and leave your comment. If there's a popular feature in demand, I may consider incorporating it.


T-SQL Code for sp_SDS:

USE master;
GO

IF OBJECT_ID('dbo.sp_SDS', 'P') IS NOT NULL
  DROP PROCEDURE dbo.sp_SDS;
GO

CREATE PROCEDURE dbo.sp_SDS
  @TargetDatabase sysname = NULL,     --  NULL: all dbs
  @Level varchar(10) = 'Database',    --  or "File"
  @UpdateUsage bit = 0,               --  default no update
  @Unit char(2) = 'KB'                --  Megabytes, Kilobytes or Gigabytes
  WITH ENCRYPTION
AS

/***************************************************************************************************************
**
**  Author:    Richard Ding
**  Created:   4/8/2008
**  Updated:
**     8/20/2010 - Fix "Arithmetic overflow error converting numeric to data type numeric"
**                 when IsNumericRoundAbortEnabled turns on, it error out when loss of precision
**                 Replace HAS_DBACCESS with serverproperty function as the former is permission
**     3/17/2015 - Added SQL 2012 and 2014 in version checking
**     5/4/2017 - Added SQL 2016 and 2017 in version checking
**     1/23/2018 - Expanded size to petabytes (PB) with dec (18, 2).
**                 Added potential future SQL 2018/2020 in version checking         
**  Usage:     list db/file size AND path w/o summary
**  Refer to:  http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1313431,00.html
**  Test code: sp_SDS   --  default behavior
**             sp_SDS 'master'
**             sp_SDS NULL, NULL, 0
**             sp_SDS NULL, 'file', 1, 'GB'
**             sp_SDS 'Test_snapshot', 'Database', 1
**             sp_SDS 'adventureworks', 'File', 0, 'kb'
**             sp_SDS 'Finance', 'Database', 0, 'gb'
**             sp_SDS 'tempdb', NULL, 1, 'kb'
** 
***************************************************************************************************************/

SET NOCOUNT ON;

IF @TargetDatabase IS NOT NULL AND DB_ID(@TargetDatabase) IS NULL
  BEGIN
    RAISERROR(15010, -1, -1, @TargetDatabase);
    RETURN (-1)
  END

IF OBJECT_ID('tempdb.dbo.##Tbl_CombinedInfo', 'U') IS NOT NULL
  DROP TABLE dbo.##Tbl_CombinedInfo;

IF OBJECT_ID('tempdb.dbo.##Tbl_DbFileStats', 'U') IS NOT NULL
  DROP TABLE dbo.##Tbl_DbFileStats;

IF OBJECT_ID('tempdb.dbo.##Tbl_ValidDbs', 'U') IS NOT NULL
  DROP TABLE dbo.##Tbl_ValidDbs;

IF OBJECT_ID('tempdb.dbo.##Tbl_Logs', 'U') IS NOT NULL
  DROP TABLE dbo.##Tbl_Logs;

CREATE TABLE dbo.##Tbl_CombinedInfo (
  DatabaseName sysname NULL,
  [type] VARCHAR(10) NULL,
  LogicalName sysname NULL,
  T dec(18, 2) NULL,
  U dec(18, 2) NULL,
  [U(%)] dec(5, 2) NULL,
  F dec(18, 2) NULL,
  [F(%)] dec(5, 2) NULL,
  PhysicalName sysname NULL );

CREATE TABLE dbo.##Tbl_DbFileStats (
  Id int identity,
  DatabaseName sysname NULL,
  FileId int NULL,
  FileGroup int NULL,
  TotalExtents bigint NULL,
  UsedExtents bigint NULL,
  Name sysname NULL,
  FileName varchar(255) NULL );

CREATE TABLE dbo.##Tbl_ValidDbs (
  Id int identity,
  Dbname sysname NULL );

CREATE TABLE dbo.##Tbl_Logs (
  DatabaseName sysname NULL,
  LogSize dec(18, 2) NULL,
  LogSpaceUsedPercent dec (5, 2) NULL,
  Status int NULL );

DECLARE @Ver varchar(15),
        @DatabaseName sysname,
        @Ident_last int,
        @String varchar(2000),
        @BaseString varchar(2000);
     
SELECT @DatabaseName = '',
       @Ident_last = 0,
       @String = '',
       @Ver = CASE WHEN @@VERSION LIKE '%8.0%' THEN 'SQL 2000'
               WHEN @@VERSION LIKE '%9.0%' THEN 'SQL 2005'
                   WHEN @@VERSION LIKE '%10.0%' THEN 'SQL 2008'
                   WHEN @@VERSION LIKE '%10.50%' THEN 'SQL 2008 R2'
   WHEN @@VERSION LIKE '%11.0%' THEN 'SQL 2012'
   WHEN @@VERSION LIKE '%12.0%' THEN 'SQL 2014'
   WHEN @@VERSION LIKE '%13.0%' THEN 'SQL 2016'
   WHEN @@VERSION LIKE '%14.0%' THEN 'SQL 2017'
   WHEN @@VERSION LIKE '%15.0%' THEN 'SQL 2018'    --  guess
   WHEN @@VERSION LIKE '%16.0%' THEN 'SQL 2019'    --  guess
   WHEN @@VERSION LIKE '%17.0%' THEN 'SQL 2020'    --  guess
              END;
           
SELECT @BaseString =
' SELECT DB_NAME(), ' +
CASE WHEN @Ver = 'SQL 2000' THEN 'CASE WHEN status & 0x40 = 0x40 THEN ''Log''  ELSE ''Data'' END'
  ELSE ' CASE type WHEN 0 THEN ''Data'' WHEN 1 THEN ''Log'' WHEN 4 THEN ''Full-text'' ELSE ''reserved'' END' END +
', name, ' +
CASE WHEN @Ver = 'SQL 2000' THEN 'filename' ELSE 'physical_name' END +
', size*8.0/1024.0 FROM ' +
CASE WHEN @Ver = 'SQL 2000' THEN 'sysfiles' ELSE 'sys.database_files' END +
' WHERE '
+ CASE WHEN @Ver = 'SQL 2000' THEN ' HAS_DBACCESS(DB_NAME()) = 1' ELSE 'state_desc = ''ONLINE''' END + '';

SELECT @String = 'INSERT INTO dbo.##Tbl_ValidDbs SELECT name FROM ' +
                 CASE WHEN @Ver = 'SQL 2000' THEN 'master.dbo.sysdatabases'
                      WHEN @Ver IN ('SQL 2005', 'SQL 2008', 'SQL 2008 R2', 'SQL 2012', 'SQL 2014', 'SQL 2016', 'SQL 2017', 'SQL 2018', 'SQL 2019', 'SQL 2020')
  THEN 'master.sys.databases'
                 END + ' WHERE DATABASEPROPERTYEX (name,''Status'') = ''ONLINE''  ORDER BY name ASC';
EXEC (@String);

INSERT INTO dbo.##Tbl_Logs EXEC ('DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS');

--  For data part
IF @TargetDatabase IS NOT NULL
  BEGIN
    SELECT @DatabaseName = @TargetDatabase;
    IF @UpdateUsage <> 0 AND DATABASEPROPERTYEX (@DatabaseName,'Status') = 'ONLINE'
          AND DATABASEPROPERTYEX (@DatabaseName, 'Updateability') <> 'READ_ONLY'
      BEGIN
        SELECT @String = 'USE [' + @DatabaseName + '] DBCC UPDATEUSAGE (0)';
        PRINT '*** ' + @String + ' *** ';
        EXEC (@String);
        PRINT '';
      END
   
    SELECT @String = 'INSERT INTO dbo.##Tbl_CombinedInfo (DatabaseName, type, LogicalName, PhysicalName, T) ' + @BaseString;

    INSERT INTO dbo.##Tbl_DbFileStats (FileId, FileGroup, TotalExtents, UsedExtents, Name, FileName)
          EXEC ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS');
    EXEC ('USE [' + @DatabaseName + '] ' + @String);
     
    UPDATE dbo.##Tbl_DbFileStats SET DatabaseName = @DatabaseName;
  END
ELSE
  BEGIN
    WHILE 1 = 1
      BEGIN
        SELECT TOP 1 @DatabaseName = Dbname FROM dbo.##Tbl_ValidDbs WHERE Dbname > @DatabaseName ORDER BY Dbname ASC;
        IF @@ROWCOUNT = 0
          BREAK;
        IF @UpdateUsage <> 0 AND DATABASEPROPERTYEX (@DatabaseName, 'Status') = 'ONLINE'
              AND DATABASEPROPERTYEX (@DatabaseName, 'Updateability') <> 'READ_ONLY'
          BEGIN
            SELECT @String = 'DBCC UPDATEUSAGE (''' + @DatabaseName + ''') ';
            PRINT '*** ' + @String + '*** ';
            EXEC (@String);
            PRINT '';
          END
 
        SELECT @Ident_last = ISNULL(MAX(Id), 0) FROM dbo.##Tbl_DbFileStats;

        SELECT @String = 'INSERT INTO dbo.##Tbl_CombinedInfo (DatabaseName, type, LogicalName, PhysicalName, T) ' + @BaseString;

        EXEC ('USE [' + @DatabaseName + '] ' + @String);
   
        INSERT INTO dbo.##Tbl_DbFileStats (FileId, FileGroup, TotalExtents, UsedExtents, Name, FileName)
          EXEC ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS');

        UPDATE dbo.##Tbl_DbFileStats SET DatabaseName = @DatabaseName WHERE Id BETWEEN @Ident_last + 1 AND @@IDENTITY;
      END
  END

--  set used size for data files, do not change total obtained from sys.database_files as it has for log files
UPDATE dbo.##Tbl_CombinedInfo
SET U = s.UsedExtents*8*8/1024.0
FROM dbo.##Tbl_CombinedInfo t JOIN dbo.##Tbl_DbFileStats s
ON t.LogicalName = s.Name AND s.DatabaseName = t.DatabaseName;

--  set used size and % values for log files:
UPDATE dbo.##Tbl_CombinedInfo
SET [U(%)] = LogSpaceUsedPercent,
    U = T * LogSpaceUsedPercent/100.0
FROM dbo.##Tbl_CombinedInfo t JOIN dbo.##Tbl_Logs l
ON l.DatabaseName = t.DatabaseName
WHERE t.type = 'Log';

UPDATE dbo.##Tbl_CombinedInfo SET F = T - U, [U(%)] = U*100.0/T;

UPDATE dbo.##Tbl_CombinedInfo SET [F(%)] = F*100.0/T;

IF UPPER(ISNULL(@Level, 'DATABASE')) = 'FILE'
  BEGIN
    IF @Unit = 'KB'
      UPDATE dbo.##Tbl_CombinedInfo
      SET T = T * 1024, U = U * 1024, F = F * 1024;
   
    IF @Unit = 'GB'
      UPDATE dbo.##Tbl_CombinedInfo
      SET T = T / 1024, U = U / 1024, F = F / 1024;
   
    SELECT DatabaseName AS 'Database',
      type AS 'Type',
      LogicalName,
      T AS 'Total',
      U AS 'Used',
      [U(%)] AS 'Used (%)',
      F AS 'Free',
      [F(%)] AS 'Free (%)',
      PhysicalName
      FROM dbo.##Tbl_CombinedInfo
      WHERE DatabaseName LIKE ISNULL(@TargetDatabase, '%')
      ORDER BY DatabaseName ASC, type ASC;

    SELECT CASE WHEN @Unit = 'GB' THEN 'GB' WHEN @Unit = 'KB' THEN 'KB' ELSE 'MB' END AS 'SUM',
        SUM (T) AS 'TOTAL', SUM (U) AS 'USED', SUM (F) AS 'FREE' FROM dbo.##Tbl_CombinedInfo;
  END

IF UPPER(ISNULL(@Level, 'DATABASE')) = 'DATABASE'
  BEGIN
    DECLARE @Tbl_Final TABLE (
      DatabaseName sysname NULL,
      TOTAL dec(18, 2),
      [=] char(1),
      used dec(18, 2),
      [used (%)] dec (5, 2),
      [+] char(1),
      free dec(18, 2),
      [free (%)] dec (5, 2),
      [==] char(2),
      Data dec(18, 2),
      Data_Used dec(18, 2),
      [Data_Used (%)] dec (5, 2),
      Data_Free dec(18, 2),
      [Data_Free (%)] dec (5, 2),
      [++] char(2),
      Log dec(18, 2),
      Log_Used dec(18, 2),
      [Log_Used (%)] dec (5, 2),
      Log_Free dec(18, 2),
      [Log_Free (%)] dec (5, 2) );

    INSERT INTO @Tbl_Final
      SELECT x.DatabaseName,
           x.Data + y.Log AS 'TOTAL',
           '=' AS '=',
           x.Data_Used + y.Log_Used AS 'U',
           (x.Data_Used + y.Log_Used)*100.0 / (x.Data + y.Log)  AS 'U(%)',
           '+' AS '+',
           x.Data_Free + y.Log_Free AS 'F',
           (x.Data_Free + y.Log_Free)*100.0 / (x.Data + y.Log)  AS 'F(%)',
           '==' AS '==',
           x.Data,
           x.Data_Used,
           x.Data_Used*100/x.Data AS 'D_U(%)',
           x.Data_Free,
           x.Data_Free*100/x.Data AS 'D_F(%)',
           '++' AS '++',
           y.Log,
           y.Log_Used,
           y.Log_Used*100/y.Log AS 'L_U(%)',
           y.Log_Free,
           y.Log_Free*100/y.Log AS 'L_F(%)'
      FROM
      ( SELECT d.DatabaseName,
               SUM(d.T) AS 'Data',
               SUM(d.U) AS 'Data_Used',
               SUM(d.F) AS 'Data_Free'
          FROM dbo.##Tbl_CombinedInfo d WHERE d.type = 'Data' GROUP BY d.DatabaseName ) AS x
      JOIN
      ( SELECT l.DatabaseName,
               SUM(l.T) AS 'Log',
               SUM(l.U) AS 'Log_Used',
               SUM(l.F) AS 'Log_Free'
          FROM dbo.##Tbl_CombinedInfo l WHERE l.type = 'Log' GROUP BY l.DatabaseName ) AS y
      ON x.DatabaseName = y.DatabaseName;
 
    IF @Unit = 'KB'
      UPDATE @Tbl_Final SET TOTAL = TOTAL * 1024,
      used = used * 1024,
      free = free * 1024,
      Data = Data * 1024,
      Data_Used = Data_Used * 1024,
      Data_Free = Data_Free * 1024,
      Log = Log * 1024,
      Log_Used = Log_Used * 1024,
      Log_Free = Log_Free * 1024;
   
     IF @Unit = 'GB'
      UPDATE @Tbl_Final SET TOTAL = TOTAL / 1024,
      used = used / 1024,
      free = free / 1024,
      Data = Data / 1024,
      Data_Used = Data_Used / 1024,
      Data_Free = Data_Free / 1024,
      Log = Log / 1024,
      Log_Used = Log_Used / 1024,
      Log_Free = Log_Free / 1024;
   
      DECLARE @GrantTotal dec(18, 2);
      SELECT @GrantTotal = SUM(TOTAL) FROM @Tbl_Final;

      SELECT
      CONVERT(dec(18, 2), TOTAL*100.0/@GrantTotal) AS 'WEIGHT (%)',
      DatabaseName AS 'DATABASE',
  Databasepropertyex (DatabaseName, 'recovery') as 'Recovery Model',
      CONVERT(VARCHAR(12), used) + '  (' + CONVERT(VARCHAR(12), [used (%)]) + ' %)' AS 'USED  (%)',
      [+],
      CONVERT(VARCHAR(12), free) + '  (' + CONVERT(VARCHAR(12), [free (%)]) + ' %)' AS 'FREE  (%)',
      [=],
      TOTAL,
      [=],
      CONVERT(VARCHAR(12), Data) + '  (' + CONVERT(VARCHAR(12), Data_Used) + ',  ' +
      CONVERT(VARCHAR(12), [Data_Used (%)]) + '%)' AS 'DATA  (used,  %)',
      [+],
      CONVERT(VARCHAR(12), Log) + '  (' + CONVERT(VARCHAR(12), Log_Used) + ',  ' +
      CONVERT(VARCHAR(12), [Log_Used (%)]) + '%)' AS 'LOG  (used,  %)'
        FROM @Tbl_Final
        WHERE DatabaseName LIKE ISNULL(@TargetDatabase, '%')
        ORDER BY DatabaseName ASC;
     
    IF @TargetDatabase IS NULL
      SELECT CASE WHEN @Unit = 'GB' THEN 'GB' WHEN @Unit = 'KB' THEN 'KB' ELSE 'MB' END AS 'SUM',
      SUM (used) AS 'USED',
      SUM (free) AS 'FREE',
      SUM (TOTAL) AS 'TOTAL',
      SUM (Data) AS 'DATA',
      SUM (Log) AS 'LOG'
      FROM @Tbl_Final;
  END

RETURN (0)

GO