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.