Category Archives: T-SQL Scripting Series

Contains blog posts about Transact SQL Scripts that I think are worth sharing with the online community.

Generate new predictable unique identifiers from existing ones

Introduction

Today I want to share a script with you that I used to copy some database rows that use unique identifiers (or guids).
I had related rows that I needed to copy (compare with OrderHeader / OrderDetail where OrderDetail contains the OrderHeaderGuid)

Problem

When you want to copy related records that are identified with unique identifiers, you have a dependency on the unique identifier values that are used in other tables as foreign keys.
Especially when you want to copy multiple rows (having multiple different unique identifiers) creating new predictable guids from the existing ones can be useful.
In the example below I explain how this can be done using a MD5 hash of the source guid.
The example uses the AdventureWorks database.

Solution

TSQL-E04-script.sql

--\
---) Script: Generate new predictable GUIDs from existing ones - demo
---) Author: Hans Michiels
---) Website: www.hansmichiels.com
--/

--\
---) 1. Prepare for the demo
--/
USE [tempdb]
GO

IF OBJECT_ID('[dbo].[SalesOrderHeader]', 'U') IS NOT NULL
   DROP TABLE [dbo].[SalesOrderHeader];
IF OBJECT_ID('[dbo].[SalesOrderDetail]', 'U') IS NOT NULL
   DROP TABLE [dbo].[SalesOrderDetail];
GO

SELECT NEWID() AS [SalesOrderGUID], h.*
INTO [dbo].[SalesOrderHeader]
FROM [AdventureWorks].[Sales].[SalesOrderHeader] h

SELECT h.SalesOrderGUID, NEWID() AS [SalesOrderDetailGUID], d.*
INTO [dbo].[SalesOrderDetail]
FROM [AdventureWorks].[Sales].[SalesOrderDetail] d
JOIN [dbo].[SalesOrderHeader] h ON h.SalesOrderID = d.SalesOrderID

ALTER TABLE [tempdb].[dbo].[SalesOrderHeader] DROP COLUMN SalesOrderID;
ALTER TABLE [tempdb].[dbo].[SalesOrderDetail] DROP COLUMN SalesOrderID;
ALTER TABLE [tempdb].[dbo].[SalesOrderDetail] DROP COLUMN SalesOrderDetailID;
GO

--\
---) 2. Demonstrate how it works given the example that you would
---) want to copy an existing SalesOrder and its detail rows to
---) a new one, without having the dependency to insert the
---) header first, then retrieve the value for [SalesOrderGUID]
---) for the row, and then use it to insert the detail rows.
---) Using a new predictable unique identifier this lookup is
---) not needed.
--/

-- First we declare a value to use for 'Salt'. This makes sure that we could
-- copy the same source rows multiple times to new rows, because the generated
-- guids will be different every time the script runs.
-- We keep it simple and just use a timestamp as a salt value.
DECLARE @NewGuidSalt VARCHAR(30) = CONVERT(VARCHAR, GETDATE(), 120)
PRINT @NewGuidSalt

-- Again I keep the example very simple, hypothetical almost because the action
-- below is not likely for a real life example: we select 3 salesorders from a specific CustomerID and copy them to a different CustomerID.
-- However it explains the concept.
DECLARE @SourceCustomerID INT
DECLARE @TargetCustomerID INT

SELECT
  @SourceCustomerID = 11000
, @TargetCustomerID = 99999

-- Obviously it is important to run the entire operation
-- in a transaction and implement TRY CATCH to rollback
-- the transaction when an error occurs. For instance
-- a hash collision could occur (but the chance that it
-- happens is astronomically small).
BEGIN TRY

    BEGIN TRAN

    --\
    ---) 2a. Copy [SalesOrderHeader]
    --/
    INSERT INTO [dbo].[SalesOrderHeader]
      ( [SalesOrderGUID]
      , [RevisionNumber]
      , [OrderDate]
      , [DueDate]
      , [ShipDate]
      , [Status]
      , [OnlineOrderFlag]
      , [SalesOrderNumber]
      , [PurchaseOrderNumber]
      , [AccountNumber]
      , [CustomerID]
      , [SalesPersonID]
      , [TerritoryID]
      , [BillToAddressID]
      , [ShipToAddressID]
      , [ShipMethodID]
      , [CreditCardID]
      , [CreditCardApprovalCode]
      , [CurrencyRateID]
      , [SubTotal]
      , [TaxAmt]
      , [Freight]
      , [TotalDue]
      , [Comment]
      , [rowguid]
      , [ModifiedDate]
      )
    SELECT
        CONVERT(UNIQUEIDENTIFIER, HASHBYTES('MD5', CONVERT(CHAR(36), src.[SalesOrderGUID]) + @NewGuidSalt)) AS [SalesOrderGUID]
      , [RevisionNumber]
      , [OrderDate]
      , [DueDate]
      , [ShipDate]
      , [Status]
      , [OnlineOrderFlag]
      , [SalesOrderNumber]
      , [PurchaseOrderNumber]
      , [AccountNumber]
      , @TargetCustomerID AS [CustomerID]
      , [SalesPersonID]
      , [TerritoryID]
      , [BillToAddressID]
      , [ShipToAddressID]
      , [ShipMethodID]
      , [CreditCardID]
      , [CreditCardApprovalCode]
      , [CurrencyRateID]
      , [SubTotal]
      , [TaxAmt]
      , [Freight]
      , [TotalDue]
      , [Comment]
      , NEWID() AS [rowguid]
      , GETDATE() AS [ModifiedDate]
    FROM
        [dbo].[SalesOrderHeader] src
    WHERE
        src.CustomerID = @SourceCustomerID

    --\
    ---) 2b. Copy [SalesOrderDetail] rows
    --/
    INSERT INTO [dbo].[SalesOrderDetail]
      ( [SalesOrderGUID]
      , [SalesOrderDetailGUID]
      , [CarrierTrackingNumber]
      , [OrderQty]
      , [ProductID]
      , [SpecialOfferID]
      , [UnitPrice]
      , [UnitPriceDiscount]
      , [LineTotal]
      , [rowguid]
      , [ModifiedDate]
      )
    SELECT
        CONVERT(UNIQUEIDENTIFIER, HASHBYTES('MD5', CONVERT(CHAR(36), src.[SalesOrderGUID]) + @NewGuidSalt)) AS [SalesOrderGUID]
      , NEWID() AS [SalesOrderDetailGUID]
      -- For [SalesOrderDetailGUID] we simply use NEWID(), because it is not used somewhere else.
      , src.[CarrierTrackingNumber]
      , src.[OrderQty]
      , src.[ProductID]
      , src.[SpecialOfferID]
      , src.[UnitPrice]
      , src.[UnitPriceDiscount]
      , src.[LineTotal]
      , NEWID() AS [rowguid]
      , GETDATE() AS [ModifiedDate]
    FROM
        [dbo].[SalesOrderDetail] src
    JOIN
        [dbo].[SalesOrderHeader] h
        ON h.SalesOrderGUID = src.SalesOrderGUID
    WHERE
        h.CustomerID = @SourceCustomerID

    COMMIT TRAN;

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE();
    RAISERROR(@ErrorMessage, 16, 1);
END CATCH

--\
---) 3. Now view the results
---) Note that the order detail rows are copied using the
---) same generated guid for [SalesOrderGUID]. Note that
---) if you would rerun the script starting with
---) “2. Demonstrate ..” you would get a second copy of the
---) same orders with orderdetails rows and with a different
---) generated guid for [SalesOrderGUID], due to the Salt
---) that was used.
--/
SELECT *
FROM
    [dbo].[SalesOrderHeader] h
WHERE
    h.CustomerID IN (@SourceCustomerID, @TargetCustomerID)

SELECT d.*
FROM
    [dbo].[SalesOrderDetail] d
JOIN
    [dbo].[SalesOrderHeader] h
    ON h.SalesOrderGUID = d.SalesOrderGUID
WHERE
    h.CustomerID IN (@SourceCustomerID, @TargetCustomerID)

GO

--\
---) 3. CLEANUP
--/
USE [tempdb]
GO

IF OBJECT_ID('[dbo].[SalesOrderHeader]', 'U') IS NOT NULL
   DROP TABLE [dbo].[SalesOrderHeader];
IF OBJECT_ID('[dbo].[SalesOrderDetail]', 'U') IS NOT NULL
   DROP TABLE [dbo].[SalesOrderDetail];
GO

Download the script here.

TSQL-E04-script
© 2018 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

How a SSIS Lookup works different from a T-SQL JOIN (SSIS Series)

Introduction

With a Lookup in a dataflow in a SSIS Package you can achieve a similar result as with a T-SQL Join clause in a SELECT statement: you can check if a row exists in a different dataset, based on one or more (business / natural / surrogate) key columns. However there are a number of differences. One of these differences took me several hours of investigating some time ago, when I had this lookup in SSIS that did not find any row. I did not understand why, because I had a similar T-SQL statement with a JOIN that found all rows. What could be different (it appeared to be a CHAR column connected to a VARCHAR)?
So today I am going to compare the two.

Subjects of comparison:

  1. Perform a lookup when the datatypes are different
  2. Perform a lookup on a different SQL Server Instance
  3. Perform a lookup when the join is not a equi-join
  4. When the source is not SQL Server
  5. When the joined columns differ in Case (Uppercase / Lowercase / Mixed case)

Simple setup for two lookup transformations using the Adventureworks database

Perform a lookup when the datatypes are different

A Transact-SQL query with a join is more forgiving than a SSIS Lookup when different datatypes are joined.

Connect a nvarchar to a varchar column

In a SSIS Lookup this does not work because an error is shown during design:

In T-SQL, this join can be made and will work:

different_datatypes.sql
SELECT
    *
FROM
    [Production].[STG_Product] stg
JOIN
    [Production].[ProductCategory] pc
    ON pc.name = stg.VARCHAR_ProductCategoryName

Connect a fixed length CHAR column to a variable length VARCHAR column (or NCHAR to NVARCHAR)

In SSIS the lookup can be developed, but will not work in practice, when the CHAR column value is not the maximum length (and thus has trailing spaces).
A “join” of a nvarchar to a nchar column ..
.. will not work !

In T-SQL, this join can be made and will work:

fixed_length_column.sql
SELECT
    *
FROM
    [Production].[STG_Product] stg
JOIN
    [Production].[ProductCategory] pc
    ON pc.name = stg.NCHAR_ProductCategoryName

Perform a lookup on a different SQL Server Instance

In a SSIS Lookup you can use different connection managers for source and Lookup transformation, so this is easy to do.

In T-SQL this would be more complex to implement, for instance you could use linked servers if your security policy allows this.

Perform a lookup when the join is not a equi-join

An equi-join is a join where the column values that are joined simply must be equal to each other.

By default a SSIS Lookup will do an equi-join. In the visual editor you connect one or more columns from the Available Input Columns to the Available Lookup Columns. Designed in this way it will be an equi-join.
There are two ways I know of to implement a different kind of join:

  1. in the Advanced Tab of the Lookup Transformation Editor you can modify the SQL statement.
  2. use a OLE DB Command in the Data Flow, but this can have a negative impact on performance.

In T-SQL you can do more complex joins, for instance with a BETWEEN clause for an EffectiveStartDate and EffectiveEndDate.

When the source is not SQL Server

In a SSIS Lookup you can use different connection managers for source and Lookup transformation, so this is easy to do.

In T-SQL this would be practicly impossible, or at least much more complex to accomplish (e.g. with OPENROWSET).

When the joined columns differ in Case

A SSIS Lookup is case-sensitive when Full cache is used, so differences in casing cause that lookup values are not found.
However when you switch to No Cache or Partial Cache , the lookup can become case-insensitive! Keith Mescha brought this to my attention (see below). Thank you, Keith.
So the safest thing to do is convert the column values for columns to connect from Available Input Columns to Available Lookup Columns to either upper- or lowercase on both sides. In T-SQL you can use the LOWER() and UPPER() functions for this, in SSIS you can add a Derived column to your dataflow, where you also can use the LOWER() and UPPER() functions. Depending on your needs, you can replace the existing column or add the upper or lower value as new column.

Example of a derived column that converts the value of a column of the Available Input Columns to uppercase.

In T-SQL the result of this join will depend on collation settings of the SQL Server Instance or column(s) affected in the join.
By default, SQL Server is installed using a case-insensitive collation (e.g. Latin1_General_CI_AS, where CI stands for Case Insensitive), and when the collation is not overruled on column-level, the join will work case-insensitively.

Performance considerations

From my own experience I know SSIS Lookups can be faster when developed correctly. This means:

  • Use full cache whenever the dataset allows this (so memory usage is acceptable given the amount of RAM on the server)
  • In the query of the lookup transformation, only include the columns that are needed in the Lookup.

Conclusion / Wrap up

In this blog post I have compared the SSIS Lookup transformation with the T-SQL Join.
In general T-SQL is more flexible and more forgiving, but when you need to go outside the SQL Server Instance for source or lookup it is easier to use a SSIS Lookup, but beware of the peculiarities of the SSIS Lookup, as described in this article.
Performance-wise, a SSIS Lookup can perform better than a T-SQL join, but this of course depends on a lot of design factors in both your Lookup as well as SQL database.

(c) 2017 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

Free download: Blog Yearbook 2016

I have made a compilation of all my blog posts of the past year, and it was more work than you would think.
Especially because it also contains a non-published new article.

After filling in the form you can download the e-book for free.

    I promise you the following:

    • I will not give or sell your email address to any third party.
    • I will not spam you.
    • If I start with a newsletter, you can easily unsubscribe or change the frequency.

    (c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

    When Shit Hits The SQL Agent Jobstep (T-SQL Scripting Series)

    Problems

    Due to a bug in SQL Server, a SQL Agent jobstep for executing an SSIS package that has a DateTime parameter, can get unusable on a Windows server with European Regional Settings, and more specific, a dd-mm-yyyy dateformat.
    The jobstep is stored with the datetime parameter value in a d-m-yyyy hh:nn:ss format.
    This gives two problems:

    1. During execution the following error occurs: String was not recognized as a valid DateTime
    2. When the jobstep is opened for editing, the same error occurs: “Microsoft SQL Server Management Studio String was not recognized as a valid DateTime. (mscorlib)”

    Solution

    Correct the jobstep in the msdb database: convert the datetime to a yyyy-mm-dd format, which does not give this error when the job is executed or when the jobstep is opened for editing.
    You have to run this script every time you have changed the jobstep using SQL Server Management Studio.

    TSQL-S01E03-WhenShitHitsTheSQL…

    --\
    ---) Author: Hans Michiels
    ---) With this script you can correct a SQL Agent jobstep that has a DateTime parameter
    ---) and would otherwise be uneditable due to a bug in SQL Server.
    ---) You have to run this script every time you have changed the jobstep using SQL Server Management Studio.
    --/
    /*
    (c) Copyright 2016 - hansmichiels.com
     
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.
     
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
    GNU General Public License for more details.
     
    You should have received a copy of the GNU General Public License
    along with this program. If not, see http://www.gnu.org/licenses/.
    */

    UPDATE js

      SET command = REPLACE(js.command, expr3.original_date, expr5.corrected_date)

    FROM [msdb].[dbo].[sysjobsteps] AS js

      CROSS APPLY ( SELECT
          CHARINDEX('(DateTime)', js.command) AS [datetime_pos]
          ) AS expr1

      CROSS APPLY ( SELECT
          CHARINDEX(';”\”', js.command, expr1.datetime_pos + 1) AS [start_pos],
          CHARINDEX(' ', js.command, expr1.datetime_pos + 1) AS [end_pos]
          ) AS expr2

      CROSS APPLY ( SELECT
          SUBSTRING(js.command, expr2.start_pos + 4, expr2.end_pos - start_pos - 4) AS [original_date]
          ) AS expr3

      CROSS APPLY ( SELECT
          CASE
          WHEN SUBSTRING(expr3.[original_date], 2, 1) = '-' AND SUBSTRING(expr3.[original_date], 4, 1) = '-'
            THEN '0' + SUBSTRING(expr3.[original_date], 1, 1) + '-0' + SUBSTRING(expr3.[original_date], 3, 6)
          WHEN SUBSTRING(expr3.[original_date], 2, 1) = '-' AND SUBSTRING(expr3.[original_date], 5, 1) = '-'
            THEN '0' + expr3.[original_date]
          WHEN SUBSTRING(expr3.[original_date], 3, 1) = '-' AND SUBSTRING(expr3.[original_date], 5, 1) = '-'
            THEN SUBSTRING(expr3.[original_date], 1, 3) + '0' + SUBSTRING(expr3.[original_date], 4, 6)
          ELSE expr3.[original_date]
          END AS [fixedwidth_date]
          ) AS expr4

      CROSS APPLY ( SELECT
          CONVERT(CHAR(10),
            CONVERT(DATE, SUBSTRING(expr4.[fixedwidth_date], 7, 4)
                        + SUBSTRING(expr4.[fixedwidth_date], 4, 2)
                        + SUBSTRING(expr4.[fixedwidth_date], 1, 2), 112)
                        , 120) AS [corrected_date]
        ) expr5

    WHERE js.subsystem = 'SSIS'
      AND expr1.datetime_pos > 0
      AND expr2.start_pos > 0
      AND expr2.end_pos > expr2.start_pos
      AND SUBSTRING(expr4.fixedwidth_date, 3, 1) = '-'
      AND SUBSTRING(expr4.fixedwidth_date, 6, 1) = '-'

    Download the script here.

    Known limitations

    This script works only for jobsteps with only one DateTime parameter.

    Conclusion / Wrap up

    With this script you can correct a SQL Agent jobstep that has a DateTime parameter and would otherwise be uneditable due to a bug in SQL Server.
    You have to run this script every time you have changed the jobstep using SQL Server Management Studio.

    (c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

    Schedule Ola Hallengren’s Maintenance Solution (T-SQL Scripting Series)

    Introduction

    If you have you ever struggled with setting up a maintenance plan for SQL Server, you’ll be glad that Ola Hallengren came around. This very clever SQL Server DBA thought, well the SQL Server Maintenance Plans aren’t that great. It does the job, but out of the box some jobs are scheduled at exactly the same time, amongst other inefficiencies. We all can be thankful that he wrote his own MaintenanceSolution.sql, which has won several awards and can be downloaded for free here.

    F5 and then?

    If you have downloaded Ola Hallengren’s MaintenanceSolution.sql, you still have to schedule the SQL Agent Jobs. But it is logical that this is not included, as every company, every server could have different requirements for backup, integrity checks, index optimization and statistics maintenance.

    .. Configure!

    In the script below you will find a CONFIGURATION section where you can configure how the SQL Agent Jobs created by Ola Hallengren should be scheduled. Just for your convenience, hope you like it.

    Ola_Scheduling.sql

    --\
    ---) Author: Hans Michiels
    ---) Creates jobschedules in SQL Server Agent jobs that
    ---) were created by Ola Hallengren's maintenance solution.
    --/
    /*
    (c) Copyright 2016 - hansmichiels.com
     
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.
     
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
    GNU General Public License for more details.
     
    You should have received a copy of the GNU General Public License
    along with this program. If not, see http://www.gnu.org/licenses/.
    */

    USE [msdb]
    GO

    --\-------------------------------------------------------------------------------------
    ---) DECLARATIONS
    --/-------------------------------------------------------------------------------------
    DECLARE @cur_days VARCHAR(50)
         -- @cur_days contains a space separated list of weekdays, e.g. 'mon tue wed thu fri sat sun',
         -- or use 'all' for all days.
         -- Please note that using 'mon tue wed thu fri sat sun' will create a weekly job with
         -- all days checked while using 'all' will create a daily job.

    DECLARE @cur_start_time INT
         -- @cur_start_time: the starttime of the job in integer format (hhmmss)

    DECLARE @cur_subday_interval INT
         -- @cur_subday_interval: N in 'every N seconds/minutes/hours (for seconds max N = 100)
         -- Use 0 when N/A

    DECLARE @cur_intraday_unit VARCHAR(7)
         -- @cur_intraday_unit: seconds (s), minutes (m) or hours (h).
         -- For configuration you can use both the full name or the first letter.

    DECLARE @cur_delete_any_schedule CHAR(1)
         -- @cur_delete_any_schedule: indicates if any schedule for the given jobname should be deleted.
         -- Use NULL if you want to use the default value (recommended).
         -- It is recommended only to use 'N' if you use multiple schedules for the same job.

    DECLARE @default_delete_any_schedule CHAR(1)
         -- @default_delete_any_schedule: valued used if in the configuration below
         -- [delete_any_schedule] is NULL for a specific job.

    DECLARE @delete_schedules_only CHAR(1)
         -- @delete_schedules_only: if set to 'Y', only the existing schedules are deleted.
         -- No new schedules are made.
         -- If you use this setting, make sure to set @default_delete_any_schedule also to 'Y'
         -- without overruling it to 'N' for specific jobs.

    DECLARE @cur_schedule_id int
    DECLARE @cur_job_name SYSNAME
    DECLARE @cur_schedule_name SYSNAME
    DECLARE @cur_freq_type INT
    DECLARE @cur_freq_interval INT
    DECLARE @cur_freq_subday_type INT

    DECLARE @active_start_date INT
    DECLARE @message VARCHAR(500)

    SET @active_start_date = CONVERT(INT, CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112));

    --\-------------------------------------------------------------------------------------
    ---) CONFIGURATION
    --/-------------------------------------------------------------------------------------
    SET @default_delete_any_schedule = 'Y';
    -- If you DO NOT want to delete all existing schedules for all jobs in your configuration,
    -- change @default_delete_any_schedule to 'N'.
    -- Keeping this value on 'Y' is usually safe to do and this prevents that old schedules
    -- might in some cases not be deleted which can give trouble due to multiple (conflicting)
    -- schedules.
    -- !! ONLY IF you have more than one schedule for a job, you should set [delete_any_schedule]
    -- in the configuration above to 'N' for that specific job.

    SET @delete_schedules_only = 'N';

    DECLARE daily_weekly_cursor CURSOR LOCAL STATIC FOR
    SELECT CONVERT(SYSNAME, NULL) AS [job_name], CONVERT(SYSNAME, NULL) AS [schedule_name],
            CONVERT(INT, NULL) AS [start_time], CONVERT(VARCHAR(50), NULL) AS [days],
            CONVERT(INT, NULL) AS [subday_interval],
            CONVERT(VARCHAR(7), NULL) AS [intraday_unit],
            CONVERT(CHAR(1), NULL) AS [delete_any_schedule]

    -------------[job_name]------------------------------------[schedule]--[start]-[days]-----------------------[subday ]-[intraday]-[delete_any]
    -----------------------------------------------------------[name ]--[time ]------------------------------[interval]-[unit ]-[schedule ]
    UNION SELECT 'CommandLog Cleanup' , 'Schedule', 180000, 'wed sun' , 0 , NULL , NULL
    UNION SELECT 'Output File Cleanup' , 'Schedule', 180500, 'wed sun' , 0 , NULL , NULL
    UNION SELECT 'sp_delete_backuphistory' , 'Schedule', 181000, 'wed sun' , 0 , NULL , NULL
    UNION SELECT 'sp_purge_jobhistory' , 'Schedule', 181500, 'wed sun' , 0 , NULL , NULL

    UNION SELECT 'DatabaseIntegrityCheck - SYSTEM_DATABASES' , 'Schedule', 182000, 'wed sun' , 0 , NULL , NULL
    UNION SELECT 'DatabaseIntegrityCheck - USER_DATABASES' , 'Schedule', 191500, 'wed sun' , 0 , NULL , NULL
    UNION SELECT 'DatabaseBackup - SYSTEM_DATABASES - FULL' , 'Schedule', 001500, 'all' , 0 , NULL , NULL
    UNION SELECT 'DatabaseBackup - USER_DATABASES - FULL' , 'Schedule', 003000, 'all' , 0 , NULL , NULL

    UNION SELECT 'IndexOptimize - USER_DATABASES' , 'Schedule', 081000, 'mon tue wed thu fri sat sun', 0 , NULL , NULL

    /* -- Weekly full, other days diff example:
    UNION SELECT 'DatabaseBackup - USER_DATABASES - FULL' , 'Schedule', 201500, 'fri' , 0 , NULL , NULL
    UNION SELECT 'DatabaseBackup - USER_DATABASES - DIFF' , 'Schedule', 201500, 'mon tue wed thu sat sun' , 0 , NULL , NULL
    */

    /* -- Transaction log example / Example of use of [delete_any_schedule] with more than one schedule
    UNION SELECT 'DatabaseBackup - USER_DATABASES - LOG' , 'Weekdays', 000001, 'mon tue wed thu fri' , 10 , 'minutes', 'Y'
    UNION SELECT 'DatabaseBackup - USER_DATABASES - LOG' , 'Weekend' , 000001, 'sat sun' , 2 , 'hours', 'N'
    */

      

    --\-------------------------------------------------------------------------------------
    ---) CREATION OF THE SCHEDULES
    --/-------------------------------------------------------------------------------------
    OPEN daily_weekly_cursor
    FETCH NEXT FROM daily_weekly_cursor
    INTO @cur_job_name, @cur_schedule_name, @cur_start_time, @cur_days,
         @cur_subday_interval, @cur_intraday_unit, @cur_delete_any_schedule

    WHILE @@FETCH_STATUS = 0
    BEGIN

        --\
        ---) Check if it is the dummy row to specify datatypes:
        --/
        IF @cur_job_name IS NULL
        BEGIN
          -- RAISERROR('NULL', 0, 1) WITH NOWAIT;
          FETCH NEXT FROM daily_weekly_cursor
          INTO @cur_job_name, @cur_schedule_name, @cur_start_time, @cur_days,
               @cur_subday_interval, @cur_intraday_unit, @cur_delete_any_schedule
        END

        SET @message = 'Job: ' + @cur_job_name
        RAISERROR(@message , 0, 1) WITH NOWAIT;

        --\
        ---) Get the current schedule_id, if any.
        --/
        SELECT @cur_schedule_id = -1;
        WHILE @cur_schedule_id IS NOT NULL
        BEGIN
            SELECT @cur_schedule_id = (
                SELECT TOP 1 sch.schedule_id
                FROM msdb.dbo.sysschedules sch
                  JOIN msdb.dbo.sysjobschedules js
                    ON js.schedule_id = sch.schedule_id
                  JOIN msdb.dbo.sysjobs jobs
                    ON jobs.job_id = js.job_id
                 WHERE jobs.name = @cur_job_name
                   AND ( ISNULL(@cur_delete_any_schedule, @default_delete_any_schedule) = 'Y' OR sch.name = @cur_schedule_name )
                );

            --\
            ---) If schedule exists, delete it first.
            --/
            IF @cur_schedule_id IS NOT NULL
            BEGIN
                EXEC msdb.dbo.sp_delete_schedule @schedule_id = @cur_schedule_id, @force_delete = 1;
                SET @message = ' ' + 'Schedule deleted with @schedule_id = ' + CONVERT(VARCHAR, @cur_schedule_id)
                RAISERROR(@message , 0, 1) WITH NOWAIT;
            END
        END

        --\
        ---) Set @cur_freq_type depending on days specification.
        --/
        SELECT @cur_freq_type = CASE @cur_days WHEN 'all' THEN 4 ELSE 8 END;

        --\
        ---) Set @cur_freq_interval depending on days specification.
        --/
        SELECT @cur_freq_interval = 0, @cur_days = ' ' + @cur_days + ' ';
        IF CHARINDEX(' all ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 1;
        IF CHARINDEX(' sun ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 1;
        IF CHARINDEX(' mon ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 2;
        IF CHARINDEX(' tue ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 4;
        IF CHARINDEX(' wed ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 8;
        IF CHARINDEX(' thu ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 16;
        IF CHARINDEX(' fri ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 32;
        IF CHARINDEX(' sat ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 64;
            
        IF @cur_freq_interval = 0
        BEGIN
          RAISERROR('Days string does not contain any valid day abbreviations', 16, 1);
        END

        IF @delete_schedules_only != 'Y'
        BEGIN
            IF @cur_subday_interval = 0
            BEGIN
                EXEC msdb.dbo.sp_add_jobschedule @job_name=@cur_job_name, @name=@cur_schedule_name,
              @enabled=1,
              @freq_type=@cur_freq_type,
              @freq_interval=@cur_freq_interval,
              @freq_subday_type=1,
              @freq_subday_interval=0,
              @freq_relative_interval=0,
              @freq_recurrence_factor=1,
              @active_start_date=@active_start_date,
              @active_end_date=99991231,
              @active_start_time=@cur_start_time,
              @active_end_time=235959, @schedule_id = @cur_schedule_id OUTPUT;

                SET @message = ' ' + 'Schedule created for days' + @cur_days + 'at ' + CONVERT(VARCHAR, @cur_start_time)
                RAISERROR(@message , 0, 1) WITH NOWAIT;

            END ELSE BEGIN

                SELECT @cur_freq_subday_type =
                CASE LOWER(LEFT(@cur_intraday_unit, 1))
                WHEN 'h' THEN 8
                WHEN 'm' THEN 4
                WHEN 's' THEN 2
                ELSE 0
                END

                IF @cur_freq_subday_type = 0
                BEGIN
                  RAISERROR('''intraday_unit'' must contain a valid value when ''subday_interval'' contains a number greater than 0. Please check your configuration.', 16, 1);
                END

                EXEC msdb.dbo.sp_add_jobschedule @job_name=@cur_job_name, @name=@cur_schedule_name,
              @enabled=1,
              @freq_type=@cur_freq_type,
              @freq_interval=@cur_freq_interval,
              @freq_subday_type=@cur_freq_subday_type,
              @freq_subday_interval=@cur_subday_interval,
              @freq_relative_interval=0,
              @freq_recurrence_factor=1,
              @active_start_date=@active_start_date,
              @active_end_date=99991231,
              @active_start_time=@cur_start_time,
              @active_end_time=235959, @schedule_id = @cur_schedule_id OUTPUT;

                SET @message = ' ' + 'Schedule created for days' + @cur_days + 'every ' + CONVERT(VARCHAR, @cur_subday_interval) + ' ' + @cur_intraday_unit
                RAISERROR(@message , 0, 1) WITH NOWAIT;

            END
        END
        RAISERROR('-----' , 0, 1) WITH NOWAIT;
        
        SET @cur_schedule_id = NULL;
        FETCH NEXT FROM daily_weekly_cursor
        INTO @cur_job_name, @cur_schedule_name, @cur_start_time, @cur_days,
             @cur_subday_interval, @cur_intraday_unit, @cur_delete_any_schedule
    END

    CLOSE daily_weekly_cursor
    DEALLOCATE daily_weekly_cursor

    Download the script here.

    (Note: the downloadable script has slightly better indenting than the html version above).

    TSQL-S01E02-Schedule

    Conclusion / Wrap up

    With this script you can easily schedule the SQL Agent Jobs that Ola Hallengren’s MaintenanceSolution-script creates.

    (c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

    How to copy a database table structure (T-SQL Scripting Series)

    Problem

    Do you recognize the need to copy a table structure in MS SQL Server, for temporary or permanent usage?

    Most of you will know the famous

    Insert Into Example.sql
    SELECT TOP 0 *
    INTO [dbo].[some unexisting table]
    FROM [dbo].[some existing table]

    But this approach has some limitations: Computed columns change into regular columns, and primary and foreign keys are not copied. Sometimes you just need more than this.

    Solution

    So I have made a script to copy a SQL server table structure with foreign keys but without indexes.
    This script handles user defined types and computed columns gracefully.

    My credits go to Aaron Bertand (see his post on mssqltips). I used his script for copying the foreign keys and added my own parts to it (download it here).

    TSQL-S01E01-copy_table

    TSQL-S01E01-CopyTableStructure.sql

    --\
    ---) Author: Hans Michiels
    ---) Script to copy a sql server table structure with foreign keys but without indexes.
    ---) This script handles user defined types and computed columns gracefully.
    --/
    /*
    (c) Copyright 2016 - hansmichiels.com
     
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.
     
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
    GNU General Public License for more details.
     
    You should have received a copy of the GNU General Public License
    along with this program. If not, see .
    */

    --\
    ---) VARIABLES DECLARATIONS
    --/
    DECLARE @CrLf NVARCHAR(2)
    DECLARE @Indent NVARCHAR(2)
    DECLARE @nsql NVARCHAR(MAX)
    DECLARE @SimulationMode CHAR(1)
    DECLARE @SourceSchemaAndTable NVARCHAR(260)
    DECLARE @TargetSchemaAndTable NVARCHAR(260)
    DECLARE @FkNameSuffix NVARCHAR(128)
    DECLARE @TableOptions NVARCHAR(500)

    --\
    ---) CONFIGURATION: set the source and target schema/tablename here, and some other settings.
    --/
    SELECT
        @SimulationMode = 'Y' -- Use Y if you only want the SQL statement in the output window without it being executed.
      , @SourceSchemaAndTable = '[dbo].[Order]'
      , @TargetSchemaAndTable = '[dbo].[OrderCopy]'
      , @TableOptions = ' WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]'
      , @FkNameSuffix = '_' + REPLACE(CAST(NEWID() AS VARCHAR(40)), '-', '') -- A Guid is added to the foreign key name to make it unique.
      , @CrLf = CHAR(13) + CHAR(10)
      , @Indent = SPACE(2)
      -- For 'min' script use this (in case sql is near 4000 characters):
      -- , @CrLf = ' '
      -- , @Indent = ''

    --\
    ---) BUILD SQL FOR CLONING TABLE
    --/
    SELECT @nsql
        = ISNULL(@nsql, '')
        + CASE col_sequence WHEN 1 THEN
          @CrLf + 'IF OBJECT_ID(N''' + @TargetSchemaAndTable + ''', ''U'') IS NOT NULL DROP TABLE ' + @TargetSchemaAndTable + ';'
        + @CrLf + 'CREATE TABLE ' + @TargetSchemaAndTable + @CrLf + @Indent + '( ' ELSE @CrLf + @Indent + ', ' END
        + [definition]
    FROM (
          SELECT ROW_NUMBER() OVER (PARTITION BY tb.object_id ORDER BY tb.object_id, col.column_id) AS col_sequence
            , QUOTENAME(col.name) + ' '
            + COALESCE(
                'AS ' + cmp.definition + CASE ISNULL(cmp.is_persisted, 0) WHEN 1 THEN ' PERSISTED ' ELSE '' END,
                CASE
                  WHEN col.system_type_id != col.user_type_id THEN QUOTENAME(usr_tp.schema_name) + '.' + QUOTENAME(usr_tp.name)
                  ELSE
                    QUOTENAME(sys_tp.name) +
                    CASE
                      WHEN sys_tp.name IN ('char', 'varchar', 'binary', 'varbinary') THEN '(' + CONVERT(VARCHAR, CASE col.max_length WHEN -1 THEN 'max' ELSE CAST(col.max_length AS varchar(10)) END) + ')'
                      WHEN sys_tp.name IN ('nchar', 'nvarchar') THEN '(' + CONVERT(VARCHAR, CASE col.max_length WHEN -1 THEN 'max' ELSE CAST(col.max_length/2 AS varchar(10)) END) + ')'
                      WHEN sys_tp.name IN ('decimal', 'numeric') THEN '(' + CAST(col.precision AS VARCHAR) + ',' + CAST(col.scale AS VARCHAR) + ')'
                      WHEN sys_tp.name IN ('datetime2') THEN '(' + CAST(col.scale AS VARCHAR) + ')'
                      ELSE ''
                    END
                END
                )
            + CASE col.is_nullable
                WHEN 0 THEN ' NOT NULL'
                ELSE CASE WHEN cmp.definition IS NULL THEN ' NULL' ELSE ' ' END
              END AS [definition]
           FROM sys.tables tb
           JOIN sys.schemas sch
             ON sch.schema_id = tb.schema_id
           JOIN sys.columns col
             ON col.object_id = tb.object_id
           JOIN sys.types sys_tp
             ON col.system_type_id = sys_tp.system_type_id
            AND col.system_type_id = sys_tp.user_type_id
           LEFT JOIN
                (
                SELECT tp.*, sch.name AS [schema_name]
                FROM sys.types tp
                JOIN sys.schemas sch
                ON tp.schema_id = sch.schema_id
                ) usr_tp
             ON col.system_type_id = usr_tp.system_type_id
            AND col.user_type_id = usr_tp.user_type_id
           LEFT JOIN sys.computed_columns cmp
             ON cmp.object_id = tb.object_id
            AND cmp.column_id = col.column_id
          WHERE tb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U')
          ) subqry
    ;
    SELECT @nsql
        = ISNULL(@nsql, '')
        + CASE col_sequence
            WHEN 1 THEN @CrLf + ', PRIMARY KEY ' + CASE is_clustered_index WHEN 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END
               + @CrLf + @Indent + '( '
            ELSE @CrLf + @Indent + ', '
          END
        + QUOTENAME(pk_cols.column_name)
        + CASE is_descending_key
            WHEN 1 THEN ' DESC'
            ELSE ' ASC'
          END
    FROM (
          SELECT TOP 2147483647 sch.name as schema_name, tb.name as table_name, col.name as column_name
                   , ROW_NUMBER() OVER (PARTITION BY tb.object_id ORDER BY tb.object_id, ic.key_ordinal) AS col_sequence
                   , ic.is_descending_key
                   , CASE WHEN idx.index_id = 1 THEN 1 ELSE 0 END AS [is_clustered_index]
           FROM sys.tables tb
           JOIN sys.schemas sch
             ON sch.schema_id = tb.schema_id
           JOIN sys.indexes idx
             ON idx.is_primary_key = 1
            AND idx.object_id = tb.object_id
           JOIN sys.index_columns ic
             ON is_included_column = 0
            AND ic.object_id = tb.object_id
            AND ic.index_id = idx.index_id
           JOIN sys.columns col
             ON col.column_id = ic.column_id
            AND col.object_id = tb.object_id
          WHERE tb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U')
          ORDER BY tb.object_id, ic.key_ordinal
          ) pk_cols
     
    SELECT @nsql = @nsql + @CrLf + @indent + ') ' + @TableOptions

    IF @SimulationMode = 'Y'
    BEGIN
      PRINT '-- Simulation mode: script is not executed.'
    END
    PRINT @nsql;
    IF @SimulationMode != 'Y'
    BEGIN
      EXEC(@nsql);
    END
     
    --\
    ---) Copy foreign key constraints
    ---) A guid is added to the foreign key name to make it unique within the database.
    --/
    SET @nsql = N'';
    SELECT @nsql += N'
    ALTER TABLE '

          + @TargetSchemaAndTable
          + ' ADD CONSTRAINT [' + LEFT(fk.name + @FkNameSuffix, 128) + '] '
          + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(col.name)
          -- get all the columns in the constraint table
          FROM sys.columns AS col
          JOIN sys.foreign_key_columns AS fkc
            ON fkc.parent_column_id = col.column_id
            AND fkc.parent_object_id = col.[object_id]
          WHERE fkc.constraint_object_id = fk.[object_id]
          ORDER BY fkc.constraint_column_id
          FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
          + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rtb.name)
          + '('
          + STUFF((SELECT ',' + QUOTENAME(col.name)
          -- get all the referenced columns
          FROM sys.columns AS col
          JOIN sys.foreign_key_columns AS fkc
            ON fkc.referenced_column_id = col.column_id
           AND fkc.referenced_object_id = col.[object_id]
          WHERE fkc.constraint_object_id = fk.object_id
          ORDER BY fkc.constraint_column_id
          FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
    FROM sys.foreign_keys AS fk
    JOIN sys.tables AS rtb -- referenced table
       ON fk.referenced_object_id = rtb.[object_id]
    JOIN sys.schemas AS rs
       ON rtb.[schema_id] = rs.[schema_id]
    JOIN sys.tables AS ctb -- constraint table
       ON fk.parent_object_id = ctb.[object_id]
    WHERE rtb.is_ms_shipped = 0 AND ctb.is_ms_shipped = 0
      AND ctb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U');

    IF @SimulationMode = 'Y'
    BEGIN
      PRINT '-- Simulation mode: script is not executed.'
    END
    PRINT @nsql;
    IF @SimulationMode != 'Y'
    BEGIN
      EXEC(@nsql);
    END

    Download the script here.

    Conclusion / Wrap up

    With this script you can copy the structure of a table including primary and foreign keys, computed columns and columns using a user defined datatype.
    Last updated: 14 July, 2016.

    (c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.