Monday, January 16, 2017

Helpful DBA / Management Procedures / Queries for Microsoft SQL-Server

SQL Server Procedures to make DBA tasks easier and improve database quality

In this Microsoft SQL-Server related blog, I have included the source code for a few procedures that I have used to help make some common database administration / management a bit easier. Certain DBA tasks are simpler when you have these procedures that can produce a quick report to:

  • List all database tables containing data
  • List the Unique Column Definitions and Tables Where Used (and/or Discrepancies in DataTypes for Column Name)
  • List all DML INSERT/UPDATE/DELETE trigger names / definitions in Database
See below for the source code to each...

SQL-Server Stored Procedure (SP) Source Code — List all database tables containing data

Here is a useful management query to help you quickly see which tables in your database have data in them — i.e., show all tables with rowcount greater than a specified value. The procedure also makes use of parameterized ORDER BY that sorts results, by one of a few options (see source code for comments: you can choose table-name, rowcount, creation date), without using dynamic SQL.

--********************************************************************************
--This source code is Copyright (c) 2007-2017
--     Author: Mike Eberhart
--
--I hereby release this code under the terms of the MIT License (for freeware).
--
--Permission is hereby granted, free of charge, to any person obtaining a copy
--of this software and associated documentation files (the "Software"), to deal
--in the Software without restriction, including without limitation the rights
--to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
--copies of the Software, and to permit persons to whom the Software is
--furnished to do so, subject to the following conditions:
--
--The above copyright notice and this permission notice shall be included in
--all copies or substantial portions of the Software.
--
--THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
--IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
--FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
--AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
--LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
--OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
--THE SOFTWARE.
--********************************************************************************
--**********************************************************************************************
-- This procedure outputs a list of all tables containing data (i.e., non-zero row count), and
-- includes these parameters for further flexibility:
--
-- @MinimumRowCountToInclude : include only those tables containing this minimum number of rows.
-- @SortColumn: 
--  1 = ascending by table name; 
--  2 = descending by row-count, then asc by table name therein.
--  3 = descending by create-date (yyyy-mm-dd hh:mi:ss.mmm(24h)), then asc by tablename.
--    
-- TESTS:
-- uspUtilities_ListTablesWithRows 1, 1 --by table name
-- uspUtilities_ListTablesWithRows 1, 2 --by rowcount
-- uspUtilities_ListTablesWithRows 0, 3 --by creation date
-- uspUtilities_ListTablesWithRows 1000, 1 --only large tables
--
--**********************************************************************************************
CREATE PROCEDURE uspUtilities_ListTablesWithRows
    @MinimumRowCountToInclude   INT = 1,
    @SortColumn                 TINYINT = 1
AS
BEGIN

    SELECT
        o.name              AS 'Table Name',
        s.partition_number  AS 'Partition#',  
        s.row_count         AS 'Partition Row Count', 
        o.create_date       AS 'Table Creation Date'
    FROM
            sys.objects o 
      JOIN  sys.dm_db_partition_stats s 
        ON  (s.object_id = o.object_id)
    WHERE
            o.type = 'U'        --User tables
        AND s.index_id IN (0,1) --heap or clustered only
        AND s.row_count >= @MinimumRowCountToInclude
    ORDER BY 
        --to obtain proper order of numeric values, we must LEFT-PAD any numerics, and
        --all fields we ORDER BY must be seen by SQL-Server as the same DataType,
        --which is why we CONVERT / PAD all the fields to the same length / type.
        (CASE
            WHEN @SortColumn = 1 THEN CONVERT(VARCHAR(100), o.name)
            ELSE NULL
        END),
        (CASE
            WHEN @SortColumn = 2 THEN dbo.[udfGetIntegerAsPaddedString](s.row_count, 100, '0')
            ELSE NULL
        END) DESC,
        (CASE
            WHEN @SortColumn = 2 THEN CONVERT(VARCHAR(100), o.name)
            ELSE NULL
        END) ASC,
        (CASE
            WHEN @SortColumn = 3 THEN CONVERT(VARCHAR(100), o.create_date, 121)
            ELSE NULL
        END) DESC,
        (CASE
            WHEN @SortColumn = 3 THEN CONVERT(VARCHAR(100), o.name)
            ELSE NULL
        END) ASC

END


SQL-Server Stored Procedure (SP) Source Code — List Unique Column Definitions and Tables Where Used (and/or Discrepancies in DataTypes for Column Name)

A Microsoft SQL-Server Transact-SQL (T-SQL) stored procedure (SP) query that can help with your DBA (Database Administrator) tasks by helping you quickly examine all the Column-Name definitions that exist in your database.

If you are lucky enough to own a formal / professional data-modeling tool (like ERwin, PowerDesigner DataArchitect, Quest Toad Data Modeler, Embarcadero ER/Studio, etc), those tools often include handy graphical tables / reports that show column names, domains, tables, and their definitions. But, those tools are generally quite expensive and/or have a single-machine-install-license that may keep you from having access to such functionality when you need it in a hurry as you are managing your database(s). As such, queries like the one provided by this stored procedure are very helpful for analyzing your database structure / definition.

This query can list all column definitions and/or limit by column-name, and/or limit output to only those column-names where multiple (data type / specification) differences exist for the same column-name. This makes it very easy to quickly locate any column-names in your database that contain potentially conflicting data-types that could otherwise: hide potential errors, truncate data, force excess type-conversions (and thus impact performance), and so forth.

DISCUSSION:

This query / procedure makes use of modern SQL-Server features including the system object view and CTE (Common Table Expression) abilities. This could easily be adapted to older versions of SQL-Server by replacing the sys.* object references with older versions (e..g., "sysobjects"), and the CTE used in the last part of the procedure could be turned into a multi-step query using temp tables and such if needed.

This procedure also uses a technique borrowed from my Running Subtotals Example 1 in order to create the comma-delimited list of tables that make use of each unique (data type) definition of a column-name (without using a CURSOR to do so).

--**********************************************************************************************
-- This procedure outputs a list of all "unique" column definitions in the current database, 
-- where "unique" implies the same column-type, length, precision, and scale for a given 
-- column-name.
--
-- PARAMETERS:
-- @ColumnNameToExamine : include only those column(s) with name(s) as specified, or all 
--     columns if parameter is NULL.  Not case-sensitive.
-- @IncludeOnlyVariedDefinitions: when 0, no further filtration of results; when 1, returns
--     only those column names where varied column-definitions (type/scale/etc) exist for
--     the same column name.
--    
-- TESTS:
-- uspUtilities_ListUniqueColumnDefinitionsAndTablesWhereUsed             --all
-- uspUtilities_ListUniqueColumnDefinitionsAndTablesWhereUsed 'a%'        --any columns whose name begins with 'A'
-- uspUtilities_ListUniqueColumnDefinitionsAndTablesWhereUsed 'assetID'   --just 'assetID' column
-- uspUtilities_ListUniqueColumnDefinitionsAndTablesWhereUsed NULL, 1     --any column-names wih conflicting definitions
--
--**********************************************************************************************
CREATE PROCEDURE uspUtilities_ListUniqueColumnDefinitionsAndTablesWhereUsed
    @ColumnNameToExamine           VARCHAR(128) = NULL,
    @IncludeOnlyVariedDefinitions  BIT = 0
AS
BEGIN

    --**********************************************************************************************
    --Our work-table, where we can ensure the ORDER with which we later access the data, being
    --by default, the order of the PRIMARY KEY declared here in UniqueID column.
    --*********************************************************************************************
    DECLARE @column_defs TABLE
    (
        UniqueID                    INT IDENTITY NOT NULL PRIMARY KEY,
        ColumnName                  VARCHAR(128),
        ColumnTypeId                INT,
        ColumnMaxLength             SMALLINT,
        ColumnPrecision             TINYINT,
        ColumnScale                 TINYINT,
        tablesList_using_col_def    VARCHAR(MAX)
    );

    --**********************************************************************************************
    --Get all the column-specification information to consider as "unique column definitions"
    --as well as all the table names that use each unique definition of a column.
    --Limit to column-name(s) per parameter value if value is specified at runtime.
    --**********************************************************************************************
    INSERT INTO @column_defs( 
        ColumnName,
        ColumnTypeId,
        ColumnMaxLength,
        ColumnPrecision,
        ColumnScale,
        tablesList_using_col_def
        )
    SELECT DISTINCT
        columns.name,
        columns.system_type_id,
        columns.max_length,
        columns.precision, 
        columns.scale,
        tables.name
    FROM
        sys.objects tables
        JOIN sys.columns columns
          ON (tables.object_id = columns.object_id)
    WHERE
            tables.type = 'u'
        AND ((@ColumnNameToExamine IS NULL) OR (columns.name LIKE @ColumnNameToExamine));
  

    --**********************************************************************************************
    -- ALGORITHM EXPLANATION:
    --    See SET-BASED RUNNING SUBTOTALS Example 1, which this builds upon.
    --**********************************************************************************************
    DECLARE @tablesList_using_col_def VARCHAR(MAX) = '';
    DECLARE @GroupingBreakValue  VARCHAR(MAX) = '';

    UPDATE
        @column_defs
    SET
        @tablesList_using_col_def    = tablesList_using_col_def   =
            CASE 
                WHEN @GroupingBreakValue = 
                    (
                        ColumnName +  
                        dbo.[udfGetIntegerAsPaddedString](ColumnTypeId, 8,'0')    +
                        dbo.[udfGetIntegerAsPaddedString](ColumnMaxLength, 6,'0') +
                        dbo.[udfGetIntegerAsPaddedString](ColumnPrecision, 3,'0') +
                        dbo.[udfGetIntegerAsPaddedString](ColumnScale, 3,'0')
                    )
                THEN @tablesList_using_col_def + 
                    CASE
                        WHEN DATALENGTH(@tablesList_using_col_def) > 0 THEN ', ' ELSE ''
                    END +    
                    tablesList_using_col_def
                ELSE tablesList_using_col_def
            END,
        @GroupingBreakValue =
            ColumnName +  --cast to CHAR for certainty in sort...
            dbo.[udfGetIntegerAsPaddedString](ColumnTypeId, 8,'0') +
            dbo.[udfGetIntegerAsPaddedString](ColumnMaxLength, 6,'0') +
            dbo.[udfGetIntegerAsPaddedString](ColumnPrecision, 3,'0') +
            dbo.[udfGetIntegerAsPaddedString](ColumnScale, 3,'0');


    --**********************************************************************************************
    -- Group the output by unique Column-definition-per-column-name now, choosing just the row
 -- per column-def with the "max" tables-list (comma-delim list of tables using the column def)
 -- and use this result for our CTE (common table expression).
    -- Next, reference that CTE result set for our final output, and include only those column-names
 -- that meet the conditions of our parameter that can be used to filter output to include only
 -- those column definitions where *multiple* varied definitions exist for the same column-name.
    --**********************************************************************************************
  WITH ColumnInfo_CTE -- (ColumnName, ColumnType, MaxLength, Precision, Scale, TableList)
  AS
  -- Define the CTE query that includes one row per unique-column-definition (with list of tables using def).
  (
    SELECT 
        ColumnName,
        SUBSTRING(types.name,1,15)      AS 'ColumnType',
        ColumnMaxLength                 AS 'MaxLength',
        ColumnPrecision                 AS 'Precision',
        ColumnScale                     AS 'Scale',
        MAX(tablesList_using_col_def)   AS 'TablesList'
    FROM 
        @column_defs AS columns
        JOIN sys.types types
          ON (columns.ColumnTypeId = types.system_type_id )
    GROUP BY 
        ColumnName,
        SUBSTRING(types.name,1,15),
        ColumnMaxLength,
        ColumnPrecision,
        ColumnScale
  )
  -- Define the outer query referencing the CTE name: use to potentially filter results.
    SELECT 
        *
    FROM 
        ColumnInfo_CTE
 WHERE ColumnName IN (
            SELECT ColumnName FROM ColumnInfo_CTE
            GROUP BY 
                    ColumnName
            HAVING COUNT(ColumnName) > @IncludeOnlyVariedDefinitions
            ); 

END

--EXAMPLE OUTPUT demonstrating how easy it is to spot discrepancies in column-name datatype definitions:
--  EXEC uspUtilities_ListUniqueColumnDefinitionsAndTablesWhereUsed NULL, 1
--
--DatePlacedOnWeb datetime      8     23    3 Assets, Assets-old, Assets-old2
--DatePlacedOnWeb smalldatetime 4     16    0 Recipes, Recipes2
--DateUpdated     datetime      8     23    3 Assets, Assets-old, Assets-old2
--DateUpdated     smalldatetime 4     16    0 Recipes, Recipes2
--LibraryID       numeric       9     18    0   Libraries_BeforeColRemove
--LibraryID       smallint      2     5     0   Libraries, LibraryCollections



SQL-Server User-Defined-Function (UDF) Source Code — Get Common Columns (column names common to two tables)

This UDF is a nice complement to the prior utility procedure, and its output can be used within various DBA / management queries to help with migrating data between tables and other similar tasks.

This function uses metadata from some system catalog views in order to produce a list of column names that are common between two chosen tables. The output can be used in either dynamic SQL for various management tasks, or in hand-coded queries you require. In the future, if examples of how this is helpful are necessary, perhaps I will add further code here on my blog. But, hopefully it will be obvious how the output from this function is simply text (column names, separated by commas) that can be cut/paste into a query you are writing.

--****************************************************************************
-- This function will return a comma-delimited list of column-names that are  
-- common to two tables (whose names are provided in function parameters).
--
-- The output can be quite useful when performing table-maintenance tasks that
-- require a data unload/reload (e.g., rearranging columns, changing data-types),
-- as it can be used to build SQL commands like INSERT statements, etc.
--
--Input Parms: (these are the two tables to compare)
--  @table_name_1
--  @table_name_2 
--
--Returns:
--  comma delimitted list of column-names that are common to both tables
--
-- TESTS:
--  select dbo.udfUtility_GetCommonTableColumnNames('TableName1Here', 'TableName2Here')
--****************************************************************************
CREATE FUNCTION udfUtility_GetCommonTableColumnNames
(
  @table_name_1   CHAR(128),
  @table_name_2   CHAR(128)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
  --****************************************************************************
  --Make sure the two tables to compare exists.  If not, exit immediately.
  --****************************************************************************
  IF (
    SELECT ISNULL(COUNT(1),0)
    FROM
        sys.objects
    WHERE 
        (name = @table_name_1 OR name = @table_name_2)
        AND (type = 'u' )
    ) <> 2
  BEGIN
    RETURN NULL
  END


  --****************************************************************************
  -- Create the comma-delim list of shared column names now
  --****************************************************************************
  DECLARE @col_list VARCHAR(MAX) = '';

  SELECT @col_list = @col_list + 
   CASE
            WHEN DATALENGTH(@col_list) > 0 THEN ',' ELSE ''
 END +
        c1.name
    FROM
 sys.objects t1
 JOIN sys.columns c1
   ON (t1.object_id = c1.object_id),
 sys.objects t2
 JOIN sys.columns c2
   ON (t2.object_id = c2.object_id)
    WHERE
          t1.name = @table_name_1
      AND t1.type = 'u'
      AND t2.name = @table_name_2
      AND t2.type = 'u'
      AND c1.name = c2.name     --Get only those columns that are in both tables (??datatype??)
    ORDER BY
      c1.column_id ASC          --Return in column# order

  RETURN @col_list

END


SQL-Server Stored Procedure (SP) Source Code — List all DML INSERT/UPDATE/DELETE trigger names / definitions in Database

Another useful Microsoft SQL-Server Transact-SQL (T-SQL) query for DBA (Database Administration) / Management task of identifying tables with DML triggers defined, and presenting a report / list of tables with triggers and their source-code definitions. In addition, this procedure uses a CTE (Common Table Expression) to simplify sorting of the trigger information after it has been categorized into INSERT / UPDATE / DELETE columns.


--**********************************************************************************************
-- This query outputs a list of all DML INSERT/UPDATE/DELETE trigger names / definitions 
-- in the current database.  Output is grouped by Table Name with separate output columns for
-- each Trigger event-type (Insert, Update, Delete).
--
-- Also includes some logic to make use of "First" and "Last" trigger information, 
-- as can be set with a command like this:
--   dbo.sp_settriggerorder @triggername= 'trig_sysmail_server', @order='Last', @stmttype = 'UPDATE';
--
-- That information further sorts "First" to the top in a group and "Last" to the bottom for a
-- bit of visual cue as to the potential order of trigger firing.
--**********************************************************************************************

--**********************************************************************************************
-- QUERY PARAMETER VARIABLES:
-- @TableNameToExamine : include only those table(s) with name(s) as specified, or all 
--     columns if parameter is NULL.  Not case-sensitive.
--    
-- TEST VALUES FOR PARAMETERS BELOW (UN-COMMENT ONLY ONE DECLARE AT A TIME FOR GIVEN VARIBLE)
--**********************************************************************************************
DECLARE @TableNameToExamine VARCHAR(128) = NULL;    --all tables with triggers
--DECLARE @TableNameToExamine VARCHAR(128) = 'S%';    --any tables whose name begins with 'A'

WITH TriggerInfo_CTE
AS
-- Define the CTE query that includes one row per unique-column-definition (with list of tables using def).
(
    SELECT
        @@servername         AS ServerName,
        db_name()            AS DatabaseName,
        ISNULL( s.name, '' ) AS SchemaName,
        ISNULL( o.name, 'DDL Trigger' ) AS TableName,
  -- NOTE: you can use use IIF(te.is_first = 1, '!First! ', '') + IIF(te.is_last = 1, '{LAST} ', '')  
  -- in place of the CHOOSE function (in next lines) if using earlier versions of SQL-Server
        CASE WHEN te.type = 1 
            THEN CHOOSE (1 + te.is_first + te.is_last*2, '', '!First! ', '{LAST} ') + tr.name ELSE NULL 
            END AS InsertTrigger,
        CASE WHEN te.type = 2 
            THEN CHOOSE (1 + te.is_first + te.is_last*2, '', '!First! ', '{LAST} ') + tr.name ELSE NULL 
            END AS UpdateTrigger,
        CASE WHEN te.type = 3 
            THEN CHOOSE (1 + te.is_first + te.is_last*2, '', '!First! ', '{LAST} ') + tr.name ELSE NULL 
            END AS DeleteTrigger,
  IIF(tr.type = 'TR', 'SQL', 'CLR') AS Type,
        object_definition( tr.object_id ) AS TriggerDefininion
    FROM
        sys.triggers tr
        LEFT OUTER JOIN sys.all_objects o
            ON tr.parent_id = o.object_id
        LEFT OUTER JOIN sys.schemas s
            ON s.schema_id = o.schema_id
        JOIN sys.trigger_events te
            ON te.object_id = tr.object_id
    WHERE
            tr.type IN ('TR', 'TA') --TR = SQL trigger, TA = Assembly (CLR) trigger
        AND O.type = 'U'  --User Tables
        AND ((@TableNameToExamine IS NULL) OR (o.name LIKE @TableNameToExamine))
)
    -- Define the outer query referencing the CTE name: use to sort results AFTER triggers are categorized.
    SELECT 
        *
    FROM 
        TriggerInfo_CTE
        --This will sort ASC by Insert, Update, Delete Trigger-Name order within TableName! 
        --(seriously).  This is because NULLs in each column push those values downward in sort.
        ORDER BY 
            SchemaName,
            TableName,
            DeleteTrigger, 
            UpdateTrigger,
            InsertTrigger
        ; 


--EXAMPLE OUTPUT WHEN RUN AGAINST 'ReportServer' DB on SQL2012 instance (Trigger Definitions omitted due to size):
--ServerName SchemaName  DatabaseName    TableName       InsertTrigger           UpdateTrigger               DeleteTrigger                   Type
--SQL2012DEV    dbo         ReportServer    History         History_Notifications   NULL                        NULL                         SQL
--SQL2012DEV dbo         ReportServer    History         NULL                    NULL                        HistoryDelete_SnapshotRefcount SQL
--SQL2012DEV dbo         ReportServer    ReportSchedule  NULL                    NULL                        ReportSchedule_Schedule         SQL
--SQL2012DEV dbo         ReportServer    Schedule        NULL                    Schedule_UpdateExpiration NULL                            SQL
--SQL2012DEV dbo         ReportServer    Schedule        NULL                    NULL                        Schedule_DeleteAgentJob         SQL
--SQL2012DEV dbo         ReportServer    Subscriptions   NULL                    NULL                        Subscription_delete_DataSource SQL
--SQL2012DEV dbo         ReportServer    Subscriptions   NULL                    NULL                        Subscription_delete_Schedule SQL


A very basic speed-testing loop...

This is nothing but a simple framework that can be useful when optimizing and fine-tuning the execution speed of various SQL Statements, procedure/function calls, and the like. The idea is to remove the effect of caching and such, at least prior to beginning a multi-iteration loop, whereby an average run-time can then be observed over a desired number of iterations.


/***************************************************************************************/
-- This is a test-jig useful when optimizing and fine-tuning the execution speed
-- of various SQL Statements, procedure/function calls, and the likes.
-- This is especially useful when there are multiple ways to accomplish the same result, 
-- and you need to see which methods are the fastest under different iteration-counts. 
-- Just insert code to test, set the Iterations, execute, and the resulting elapsed
-- time in Milliseconds (MS) will be displayed.
--
-- For optimal speed comparisons, the data-cache and procedure-cache are cleared 
-- before running. Depending on your performance-measurement requirements, you may
-- actually want to move the first 3 lines here (the checkpoint / DBCC stuff) to
-- within the WHILE loop if you want to observer average run times of statement(s)
-- without the effect any caching has on them.
--
-- NOTE: you probably do not want to run this on a *production* system and/or should
-- consider the impact of flushing your procedure cache, etc in production.
/***************************************************************************************/
CHECKPOINT; --to produce a cold buffer cache
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; -- Remove all buffers from the buffer pool / clear data cache
DBCC FREEPROCCACHE    WITH NO_INFOMSGS; -- Clears the procedure (plan) cache

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @starttime DATETIME = GetDate();
DECLARE @Counter INT = 0;
DECLARE @Iterations INT = 100;  --Set to desired number of iterations

WHILE @Counter < @Iterations
BEGIN
    --*********************************************************
    --EXECUTE STATEMENT(S) YOU WANT TO SPEED-TEST HERE (Begin)
    --********************************************************* 
    
    SELECT 'Placeholder for statements to test here instead';
     
    --*********************************************************
    --EXECUTE STATEMENT(S) YOU WANT TO SPEED-TEST HERE (End)
    --*********************************************************
    SELECT @Counter += 1;     
END;  --While loop
        
SELECT DateDiff(ms, @starttime, GetDate()) AS 'Elapsed Milliseconds';


User-Defined Function (UDF) — Elapsed Run-Time / Debug / Tracing Utility (simplified timing and tracing info; prints well-formatted too)

A common requirement when developing custom SQL-Server Transact-SQL Stored-Procedures, Queries, and Functions is to test performance (execution speed) and display such timing information for debugging and tuning purposes at various points throughout your source code. One option for displaying this type of run-time information is to include a simple SELECT and/or PRINT statement like this everywhere you need to display such information:

    DECLARE @starttime DATETIME;
    SELECT  @starttime = GetDate();
    ...
    ... the SQL code you wish to time here...
    ...
    SELECT DateDiff(ms, @starttime, GetDate()); --Display Milliseconds elapsed from value in @starttime

...or, alternatively and arguably better (aside from dependency on this function call), you can adopt a common timing / tracing / debugging-related function like the one presented here (SQL Source Code appears below) in order to standardize the look/feel of your tracing information and display execution elapsed-times in a more consistent and easily readable format than simply milliseconds.

This function will display:

  • Start Time (DateTime) 
  • End Time (DateTime) 
  • Elapsed Time (as both ms — milliseconds — and in the equivalent hours, minutes, seconds 
... and will return a formatted string (varchar) value containing this information in a format that will PRINT in a layout just like this:

  my_debugging_Label_value_will_show_here:
      Start Time: 2006-05-03 11:04:27.280
        End Time: 2006-05-03 11:08:28.403
  Elapsed Time: 241,123 Milliseconds  ( i.e., 4 Minutes, 1 Seconds)

See my Running Subtotals Examples where this function being used within a multi-part query.


--**********************************************************************************************
--A function to help make tracing / timing simpler while producing clearer elapsed-time reports.
--
--PARAMETERS:
--  @vDebuggingLabel : an arbitrary label to display with elapsed-time information to help
--     with understanding the timing information context within your SQL execution.
--  @dtStartTime : the date/time (typically from GETDATE()) an operation you are timing began.
--  @@dtEndTime  : the date/time an operation you are timing ended : if not specified, uses current date-time via GETDATE().
--
--RETURNS:
-- Nicely formatted run-time information ready to print (by way of SELECT or PRINT statement).
-- The output of this function is a string value whose content will PRINT like this:
--
-- my_debugging_Label_value_will_show_here:   
--   Start Time: 2006-05-03 11:04:27.280     
--     End Time: 2006-05-03 11:08:28.403 
-- Elapsed Time: 241,123 Milliseconds  ( i.e., 4 Minutes, 1 Seconds)
--
--EXAMPLES / TESTS:
--PRINT [dbo].[udfUtility_PrintElapsedRuntimeDebugInfo]('TEST1 - Unspecified Start/End Dates', DEFAULT, DEFAULT);
--PRINT [dbo].[udfUtility_PrintElapsedRuntimeDebugInfo]('TEST2 - Unspecified End Date', GETDATE(), DEFAULT);
--PRINT [dbo].[udfUtility_PrintElapsedRuntimeDebugInfo]('TEST3 - Round to 1 second', '2013-10-17 18:18:03.653', '2013-10-17 18:18:04.853');
--PRINT [dbo].[udfUtility_PrintElapsedRuntimeDebugInfo]('TEST4 - Round to 2 seconds', '2013-10-17 18:18:03.653', '2013-10-17 18:18:05.453');
--PRINT [dbo].[udfUtility_PrintElapsedRuntimeDebugInfo]('TEST5 - Round to 1 minute, 2 seconds', '2013-10-17 18:18:03.653', '2013-10-17 18:19:05.453');
--PRINT [dbo].[udfUtility_PrintElapsedRuntimeDebugInfo]('TEST6 - Round to 49 Hours, 2 Minutes, 2 Seconds', '2013-10-15 18:18:03.653', '2013-10-17 19:20:05.453');
--PRINT [dbo].[udfUtility_PrintElapsedRuntimeDebugInfo]('TEST7 - backwards start/end adjusted - 1min, 2sec ', '2013-10-17 18:19:05.453', '2013-10-17 18:18:03.653');
--PRINT [dbo].[udfUtility_PrintElapsedRuntimeDebugInfo]('TEST8 - BAD DATE PARM FORMAT ERROR', 'X', DEFAULT); --Will bomb.
--**********************************************************************************************
CREATE FUNCTION [dbo].[udfUtility_PrintElapsedRuntimeDebugInfo]
    (@vDebuggingLabel   VARCHAR(500) = '',
     @dtStartTime       DATETIME = NULL,
     @dtEndTime         DATETIME = NULL) 
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @dtTemp DATETIME;

    --If specific start and/or end date-time not provided, use current time.
    SELECT  @dtStartTime = ISNULL(@dtStartTime, GETDATE());
    SELECT  @dtEndTime = ISNULL(@dtEndTime, GETDATE());

    --Handle "backwards" start/end date-times condition
    IF (@dtEndTime < @dtStartTime) 
    BEGIN
        SELECT @dtTemp = @dtStartTime;
        SELECT @dtStartTime = @dtEndTime;
        SELECT @dtEndTime = @dtTemp; 
    END;
    
    DECLARE @biElapsedMS BIGINT;
    SELECT  @biElapsedMS = DATEDIFF(ms, @dtStartTime, @dtEndTime); 
    
    DECLARE @iElapsedHH INT;
    SELECT  @iElapsedHH = @biElapsedMS / 3600000;
    
    DECLARE @iElapsedMM INT;
    SELECT  @iElapsedMM = (@biElapsedMS - (@iElapsedHH * 3600000)) / 60000; 
    
    DECLARE @iElapsedSS INT;
    SELECT  @iElapsedSS = ROUND( (@biElapsedMS - (@iElapsedHH * 3600000) - (@iElapsedMM * 60000) ), -3) / 1000;
    
    DECLARE @vElapsedMS VARCHAR(24);
    
    --use MONEY type to place commas every three digits
    SELECT @vElapsedMS = CONVERT(VARCHAR(24), CONVERT(MONEY,DATEDIFF(ms, @dtStartTime, @dtEndTime)), 1);
    --Remove the decimal and trailing zeroes
    SELECT @vElapsedMS = LEFT(@vElapsedMS, LEN(@vElapsedMS) - 3);
    
    --NOTE: if the following assignment does not copy/paste correctly, it is simply a CR (Carriage Return) within a string.
    DECLARE @vCR VARCHAR(1)
    SELECT  @vCR = '
 '
    
    --This section removes useless output of hours/minutes (if runtime was less than hour, don't show hours, etc).
    DECLARE @vHoursMsg VARCHAR(30);
    IF @biElapsedMS >= 3600000 
        SELECT @vHoursMsg = CONVERT(VARCHAR(3), @iElapsedHH) + ' Hours, ' 
    ELSE
        SELECT @vHoursMsg = '';
    
    DECLARE @vMinutesMsg VARCHAR(30)
    IF @biElapsedMS >= 60000 
        SELECT @vMinutesMsg = CONVERT(VARCHAR(2), @iElapsedMM) + ' Minutes, ' 
    ELSE
        SELECT @vMinutesMsg = '';
    
    --Send the resulting message back to the calling routine now in print-ready form
    RETURN @vDebuggingLabel + ': ' + @vCR +
        '  Start Time: ' + CONVERT(CHAR(24), CONVERT(DATETIME, @dtStartTime) , 21)  + @vCR + 
        '    End Time: ' + CONVERT(CHAR(24), CONVERT(DATETIME, @dtEndTime) , 21) + @vCR +
        'Elapsed Time: ' + @vElapsedMS + ' Milliseconds  (i.e., ' +
                           @vHoursMsg + 
                           @vMinutesMsg +
                           CONVERT(VARCHAR(2), @iElapsedSS) + ' Seconds)' + @vCR;

END


Continue to read this Software Development and Technology Blog for computer programming, software development, and technology Techniques, How-To's, Fixes, Reviews, and News — focused on Dart Language, SQL Server, Nvidia CUDA, VMware, TypeScript, SVG, other technology tips and how-to's, and my varied political and economic opinions.

No comments: