Saving Comments During Migration

During migration to SQL Server, you may find that the source database schema contains some comments to describe the schema. This can be as a comment within programming code (such as Oracle PL/SQL) in procedure, function, package, view or as a comment that is part of the table/ column schema. SQL Server Migration Assistant (SSMA) migrates the full programming code, including any comments inside procedure, function, package, view, etc. However, it does not automatically migrate comment as part of table schema. This blog post shows how to re-create the comment for tables and columns in SQL Server.

Consider the following example:

 --Execute the following in Oracle:
CREATE TABLE Orders (
    OrderID NUMBER(5),
    CustomerID NCHAR(5) ,
    EmployeeID NUMBER(5) NULL ,
    OrderDate DATE NULL ,
    RequiredDate DATE NULL 
    );

COMMENT ON TABLE Orders IS 'Customer data for the last 5 years - move to Order_archive after 5 years' ;
COMMENT ON COLUMN Orders .EmployeeID IS 'Employee Id of the person receiving the order. The value must be preserved even the employee is no longer active';
COMMENT ON COLUMN Orders .RequiredDate IS 'Date in which the product must be received at the customer site';

You can run the following query to look up the comments on Oracle table:

You can recreate the comments in SQL server using sys.sp_addextendedproperty:

 -- Execute the following in SQL Server:
CREATE TABLE dbo.Orders (
    OrderID INTEGER,
    CustomerID NCHAR(5) ,
    EmployeeID INTEGER NULL ,
    OrderDate DATETIME2 NULL ,
    RequiredDate DATETIME2 NULL 
    );

EXEC sys.sp_addextendedproperty 
@name = N'Orders', 
@value = N'Customer data for the last 5 years - move to Order_archive after 5 years', 
@level0type = N'USER', @level0name = 'dbo',
@level1type = N'TABLE', @level1name = 'Orders';
GO
EXEC sp_addextendedproperty 
@name = N'Orders.EmployeeID', 
@value = 'Employee Id of the person receiving the order. The value must be preserved even the employee is no longer active',
@level0type = N'USER', @level0name = 'dbo',
@level1type = N'Table', @level1name = 'Orders',
@level2type = N'Column', @level2name = 'EmployeeID';
GO
EXEC sp_addextendedproperty 
@name = N'Orders.RequiredDate', 
@value = 'Date in which the product must be received at the customer site',
@level0type = N'USER', @level0name = 'dbo',
@level1type = N'Table', @level1name = 'Orders',
@level2type = N'Column', @level2name = 'RequiredDate';
GO

You can query the extended properties as follow:

Special thanks to Eric Linneman from Microsoft Consulting Services for sharing the tip above. If you have any tips you like to share, please email us at ssmateam@microsoft.com.

Comments

• Anonymous
  June 27, 2013
  Hi, why you don't include table's comment as default behaviour during the conversion ??? I have a DB with almost 100 tables and is annoying to recreate every comment by hand !!!! Thanks