Snippet Name: Extract_Database_Recommendations
Tags:
Created Date: 2025-06-16 13:12:48
Last Modified Date: 2025-05-31 10:31:35
Dependencies:
DBA Notes:
Sample Output:
Title: Extract Database Recommendations
Author: user
i will paste a json string below. it is based on a Microsoft Migration Assessment.
Assume i have inserted the data into a SQL server table as seen below
i need to extract the details from the Databases array, especially the AssesmentRecommendations, which seems to ahve two layers of CompatibilityLevel and details beneath each:
CREATE TABLE #json(TheValue VARCHAR(MAX));
INSERT INTO #json (TheValue)
VALUES ('{
"Name": "S1WDVSQLMBI1.us1.autonation.com,46360",
"Databases": [
{
"ServerName": "S1WDVSQLMBI1",
"Name": "BIDBA",
"CompatibilityLevel": "CompatLevel140",
"SizeMB": 80.0,
"Status": "Completed",
"ServerVersion": "14.0.3465.1",
"AssessmentRecommendations": [
{
"CompatibilityLevel": "CompatLevel140",
"Category": "Compatibility",
"Severity": "Information",
"ChangeCategory": "BehaviorChange",
"RuleId": "Microsoft.Rules.Data.Upgrade.UR00333",
"Title": "Unqualified Join(s) detected",
"Impact": "Starting with database compatibility level 90 and higher, in rare occasions, the ''unqualified join'' syntax can cause ''missing join predicate'' warnings, leading to long running queries.",
"Recommendation": "An example of \"Unqualified join\" is\n \nselect * from table1, table2\nwhere table1.col1 = table2.col1\n\n Use explicit JOIN syntax in all cases. SQL Server supports the below explicit joins:\n - LEFT OUTER JOIN or LEFT JOIN\n- RIGHT OUTER JOIN or RIGHT JOIN\n- FULL OUTER JOIN or FULL JOIN\n- INNER JOIN",
"MoreInfo": "- Missing join Predicate Event Class (https://go.microsoft.com/fwlink/?LinkId=798567)\n- Deprecation of \"Old Style\" JOIN Syntax: Only A Partial Thing (https://go.microsoft.com/fwlink/?LinkId=798568)\n- DOC : Please strive to use ANSI-style joins instead of deprecated syntax (https://go.microsoft.com/fwlink/?LinkId=798569)\n- Missing join predicate icon should be red (https://go.microsoft.com/fwlink/?LinkId=798570)",
"ImpactedObjects": [
{
"Name": "dbo.TRIG_DIM_USER_EMAIL_GROUP_IND",
"ObjectType": "DmlTrigger",
"ImpactDetail": "Object [dbo].[TRIG_DIM_USER_EMAIL_GROUP_IND] uses the old style join syntax which can have poor performance at database compatibility level 90 and higher. For more details, please see: Line 22, Column 3.",
"SuggestedFixes": []
},
{
"Name": "dbo.sp_Subscriptions_Email",
"ObjectType": "Procedure",
"ImpactDetail": "Object [dbo].[sp_Subscriptions_Email] uses the old style join syntax which can have poor performance at database compatibility level 90 and higher. For more details, please see: Line 16, Column 3.",
"SuggestedFixes": [
{
"OrginalStatement": "SELECT a.*, b.EMAIL_ADDRESS, c.SQL_Statement\n\t\tFROM [BIDBA].[dbo].[udf_Subscriptions_Email] () a, TBL_USER_EMAIL_GROUP_BRIDGE b, TBL_SQL_REPOSITORY c\n\t\tWHERE a.email_group_id = b.EMAIL_GROUP_ID\n\t\tAND a.SQL_ID = c.SQL_ID",
"CorrectedStatement": "SELECT a.*, b.EMAIL_ADDRESS, c.SQL_Statement FROM [BIDBA].[dbo].[udf_Subscriptions_Email]() a INNER JOIN TBL_USER_EMAIL_GROUP_BRIDGE b ON a.email_group_id = b.EMAIL_GROUP_ID INNER JOIN TBL_SQL_REPOSITORY c ON a.SQL_ID = c.SQL_ID"
}
]
}
]
},
{
"CompatibilityLevel": "CompatLevel150",
"Category": "Compatibility",
"Severity": "Information",
"ChangeCategory": "BehaviorChange",
"RuleId": "Microsoft.Rules.Data.Upgrade.UR00333",
"Title": "Unqualified Join(s) detected",
"Impact": "Starting with database compatibility level 90 and higher, in rare occasions, the ''unqualified join'' syntax can cause ''missing join predicate'' warnings, leading to long running queries.",
"Recommendation": "An example of \"Unqualified join\" is\n \nselect * from table1, table2\nwhere table1.col1 = table2.col1\n\n Use explicit JOIN syntax in all cases. SQL Server supports the below explicit joins:\n - LEFT OUTER JOIN or LEFT JOIN\n- RIGHT OUTER JOIN or RIGHT JOIN\n- FULL OUTER JOIN or FULL JOIN\n- INNER JOIN",
"MoreInfo": "- Missing join Predicate Event Class (https://go.microsoft.com/fwlink/?LinkId=798567)\n- Deprecation of \"Old Style\" JOIN Syntax: Only A Partial Thing (https://go.microsoft.com/fwlink/?LinkId=798568)\n- DOC : Please strive to use ANSI-style joins instead of deprecated syntax (https://go.microsoft.com/fwlink/?LinkId=798569)\n- Missing join predicate icon should be red (https://go.microsoft.com/fwlink/?LinkId=798570)",
"ImpactedObjects": [
{
"Name": "dbo.TRIG_DIM_USER_EMAIL_GROUP_IND",
"ObjectType": "DmlTrigger",
"ImpactDetail": "Object [dbo].[TRIG_DIM_USER_EMAIL_GROUP_IND] uses the old style join syntax which can have poor performance at database compatibility level 90 and higher. For more details, please see: Line 22, Column 3.",
"SuggestedFixes": []
},
{
"Name": "dbo.sp_Subscriptions_Email",
"ObjectType": "Procedure",
"ImpactDetail": "Object [dbo].[sp_Subscriptions_Email] uses the old style join syntax which can have poor performance at database compatibility level 90 and higher. For more details, please see: Line 16, Column 3.",
"SuggestedFixes": [
{
"OrginalStatement": "SELECT a.*, b.EMAIL_ADDRESS, c.SQL_Statement\n\t\tFROM [BIDBA].[dbo].[udf_Subscriptions_Email] () a, TBL_USER_EMAIL_GROUP_BRIDGE b, TBL_SQL_REPOSITORY c\n\t\tWHERE a.email_group_id = b.EMAIL_GROUP_ID\n\t\tAND a.SQL_ID = c.SQL_ID",
"CorrectedStatement": "SELECT a.*, b.EMAIL_ADDRESS, c.SQL_Statement FROM [BIDBA].[dbo].[udf_Subscriptions_Email]() a INNER JOIN TBL_USER_EMAIL_GROUP_BRIDGE b ON a.email_group_id = b.EMAIL_GROUP_ID INNER JOIN TBL_SQL_REPOSITORY c ON a.SQL_ID = c.SQL_ID"
}
]
}
]
},
{
"CompatibilityLevel": "CompatLevel140",
"Category": "Compatibility",
"Severity": "Warning",
"ChangeCategory": "Information",
"RuleId": "Microsoft.Rules.Data.Upgrade.UR00306",
"Title": "Deprecated data types TEXT, IMAGE or NTEXT",
"Impact": "These data types are checked as deprecated. In some cases, using TEXT, IMAGE or NTEXT might harm performance.",
"Recommendation": "Deprecated data types are marked to be discontinued on next versions of SQL Server, should use new data types such as: (varchar(max), nvarchar(max), varbinary(max) and etc.)",
"MoreInfo": "ntext, text, and image (Transact-SQL) (https://go.microsoft.com/fwlink/?LinkId=798558)",
"ImpactedObjects": [
{
"Name": "dbo.sysssislog.databytes",
"ObjectType": "Column",
"ImpactDetail": "Object dbo.sysssislog.databytes uses deprecated data type TEXT, IMAGE or NTEXT which will be discontinued for future versions of SQL Server. For more details, please see: Line 12, Column 5.",
"SuggestedFixes": []
}
]
},
{
"CompatibilityLevel": "CompatLevel150",
"Category": "Compatibility",
"Severity": "Warning",
"ChangeCategory": "Information",
"RuleId": "Microsoft.Rules.Data.Upgrade.UR00306",
"Title": "Deprecated data types TEXT, IMAGE or NTEXT",
"Impact": "These data types are checked as deprecated. In some cases, using TEXT, IMAGE or NTEXT might harm performance.",
"Recommendation": "Deprecated data types are marked to be discontinued on next versions of SQL Server, should use new data types such as: (varchar(max), nvarchar(max), varbinary(max) and etc.)",
"MoreInfo": "ntext, text, and image (Transact-SQL) (https://go.microsoft.com/fwlink/?LinkId=798558)",
"ImpactedObjects": [
{
"Name": "dbo.sysssislog.databytes",
"ObjectType": "Column",
"ImpactDetail": "Object dbo.sysssislog.databytes uses deprecated data type TEXT, IMAGE or NTEXT which will be discontinued for future versions of SQL Server. For more details, please see: Line 12, Column 5.",
"SuggestedFixes": []
}
]
}
],
"ServerEdition": "Enterprise Edition (64-bit)"
}
],
"ServerInstances": [
{
"ServerName": "S1WDVSQLMBI1.us1.autonation.com",
"Version": "14.0.3465.1",
"Status": "Completed",
"AssessmentRecommendations": []
}
],
"SourcePlatform": "SqlOnPrem",
"Status": "Completed",
"StartedOn": "2024-05-17T16:52:09.4679632+00:00",
"EndedOn": "2024-05-17T17:02:06.600904+00:00",
"EvaluateFeatureRecommendations": false,
"EvaluateCompatibilityIssues": true,
"EvaluateFeatureParity": false,
"TargetPlatform": "SqlServerWindows2019",
"DmaVersion": {}
}');
Author: ChatGPT
To extract and work with the details from the `Databases` array, specifically the `AssessmentRecommendations` in the provided JSON string, you can use SQL Server's JSON functions. Here's a step-by-step guide to achieve this:
1. **Parse the JSON data**: Insert the JSON data into a table.
2. **Extract the `Databases` array**: Use `OPENJSON` to parse the JSON array.
3. **Extract the `AssessmentRecommendations`**: Use nested `OPENJSON` calls to handle the nested arrays.
Here is a SQL script to achieve this:
```sql
-- Step 1: Insert the JSON data into a table
CREATE TABLE #json(TheValue VARCHAR(MAX));
INSERT INTO #json (TheValue)
VALUES ('{
"Name": "S1WDVSQLMBI1.us1.autonation.com,46360",
"Databases": [
{
"ServerName": "S1WDVSQLMBI1",
"Name": "BIDBA",
"CompatibilityLevel": "CompatLevel140",
"SizeMB": 80.0,
"Status": "Completed",
"ServerVersion": "14.0.3465.1",
"AssessmentRecommendations": [
{
"CompatibilityLevel": "CompatLevel140",
"Category": "Compatibility",
"Severity": "Information",
"ChangeCategory": "BehaviorChange",
"RuleId": "Microsoft.Rules.Data.Upgrade.UR00333",
"Title": "Unqualified Join(s) detected",
"Impact": "Starting with database compatibility level 90 and higher, in rare occasions, the ''unqualified join'' syntax can cause ''missing join predicate'' warnings, leading to long running queries.",
"Recommendation": "An example of \"Unqualified join\" is\n \nselect * from table1, table2\nwhere table1.col1 = table2.col1\n\n Use explicit JOIN syntax in all cases. SQL Server supports the below explicit joins:\n - LEFT OUTER JOIN or LEFT JOIN\n- RIGHT OUTER JOIN or RIGHT JOIN\n- FULL OUTER JOIN or FULL JOIN\n- INNER JOIN",
"MoreInfo": "- Missing join Predicate Event Class (https://go.microsoft.com/fwlink/?LinkId=798567)\n- Deprecation of \"Old Style\" JOIN Syntax: Only A Partial Thing (https://go.microsoft.com/fwlink/?LinkId=798568)\n- DOC : Please strive to use ANSI-style joins instead of deprecated syntax (https://go.microsoft.com/fwlink/?LinkId=798569)\n- Missing join predicate icon should be red (https://go.microsoft.com/fwlink/?LinkId=798570)",
"ImpactedObjects": [
{
"Name": "dbo.TRIG_DIM_USER_EMAIL_GROUP_IND",
"ObjectType": "DmlTrigger",
"ImpactDetail": "Object [dbo].[TRIG_DIM_USER_EMAIL_GROUP_IND] uses the old style join syntax which can have poor performance at database compatibility level 90 and higher. For more details, please see: Line 22, Column 3.",
"SuggestedFixes": []
},
{
"Name": "dbo.sp_Subscriptions_Email",
"ObjectType": "Procedure",
"ImpactDetail": "Object [dbo].[sp_Subscriptions_Email] uses the old style join syntax which can have poor performance at database compatibility level 90 and higher. For more details, please see: Line 16, Column 3.",
"SuggestedFixes": [
{
"OrginalStatement": "SELECT a.*, b.EMAIL_ADDRESS, c.SQL_Statement\n\t\tFROM [BIDBA].[dbo].[udf_Subscriptions_Email] () a, TBL_USER_EMAIL_GROUP_BRIDGE b, TBL_SQL_REPOSITORY c\n\t\tWHERE a.email_group_id = b.EMAIL_GROUP_ID\n\t\tAND a.SQL_ID = c.SQL_ID",
"CorrectedStatement": "SELECT a.*, b.EMAIL_ADDRESS, c.SQL_Statement FROM [BIDBA].[dbo].[udf_Subscriptions_Email]() a INNER JOIN TBL_USER_EMAIL_GROUP_BRIDGE b ON a.email_group_id = b.EMAIL_GROUP_ID INNER JOIN TBL_SQL_REPOSITORY c ON a.SQL_ID = c.SQL_ID"
}
]
}
]
},
{
"CompatibilityLevel": "CompatLevel150",
"Category": "Compatibility",
"Severity": "Information",
"ChangeCategory": "BehaviorChange",
"RuleId": "Microsoft.Rules.Data.Upgrade.UR00333",
"Title": "Unqualified Join(s) detected",
"Impact": "Starting with database compatibility level 90 and higher, in rare occasions, the ''unqualified join'' syntax can cause ''missing join predicate'' warnings, leading to long running queries.",
"Recommendation": "An example of \"Unqualified join\" is\n \nselect * from table1, table2\nwhere table1.col1 = table2.col1\n\n Use explicit JOIN syntax in all cases. SQL Server supports the below explicit joins:\n - LEFT OUTER JOIN or LEFT JOIN\n- RIGHT OUTER JOIN or RIGHT JOIN\n- FULL OUTER JOIN or FULL JOIN\n- INNER JOIN",
"MoreInfo": "- Missing join Predicate Event Class (https://go.microsoft.com/fwlink/?LinkId=798567)\n- Deprecation of \"Old Style\" JOIN Syntax: Only A Partial Thing (https://go.microsoft.com/fwlink/?LinkId=798568)\n- DOC : Please strive to use ANSI-style joins instead of deprecated syntax (https://go.microsoft.com/fwlink/?LinkId=798569)\n- Missing join predicate icon should be red (https://go.microsoft.com/fwlink/?LinkId=798570)",
"ImpactedObjects": [
{
"Name": "dbo.TRIG_DIM_USER_EMAIL_GROUP_IND",
"ObjectType": "DmlTrigger",
"ImpactDetail": "Object [dbo].[TRIG_DIM_USER_EMAIL_GROUP_IND] uses the old style join syntax which can have poor performance at database compatibility level 90 and higher. For more details, please see: Line 22, Column 3.",
"SuggestedFixes": []
},
{
"Name": "dbo.sp_Subscriptions_Email",
"ObjectType": "Procedure",
"ImpactDetail": "Object [dbo].[sp_Subscriptions_Email] uses the old style join syntax which can have poor performance at database compatibility level 90 and higher. For more details, please see: Line 16, Column 3.",
"SuggestedFixes": [
{
"OrginalStatement": "SELECT a.*, b.EMAIL_ADDRESS, c.SQL_Statement\n\t\tFROM [BIDBA].[dbo].[udf_Subscriptions_Email] () a, TBL_USER_EMAIL_GROUP_BRIDGE b, TBL_SQL_REPOSITORY c\n\t\tWHERE a.email_group_id = b.EMAIL_GROUP_ID\n\t\tAND a.SQL_ID = c.SQL_ID",
"CorrectedStatement": "SELECT a.*, b.EMAIL_ADDRESS, c.SQL_Statement FROM [BIDBA].[dbo].[udf_Subscriptions_Email]() a INNER JOIN TBL_USER_EMAIL_GROUP_BRIDGE b ON a.email_group_id = b.EMAIL_GROUP_ID INNER JOIN TBL_SQL_REPOSITORY c ON a.SQL_ID = c.SQL_ID"
}
]
}
]
},
{
"CompatibilityLevel": "CompatLevel140",
"Category": "Compatibility",
"Severity": "Warning",
"ChangeCategory": "Information",
"RuleId": "Microsoft.Rules.Data.Upgrade.UR00306",
"Title": "Deprecated data types TEXT, IMAGE or NTEXT",
"Impact": "These data types are checked as deprecated. In some cases, using TEXT, IMAGE or NTEXT might harm performance.",
"Recommendation": "Deprecated data types are marked to be discontinued on next versions of SQL Server, should use new data types such as: (varchar(max), nvarchar(max), varbinary(max) and etc.)",
"MoreInfo": "ntext, text, and image (Transact-SQL) (https://go.microsoft.com/fwlink/?LinkId=798558)",
"ImpactedObjects": [
{
"Name": "dbo.sysssislog.databytes",
"ObjectType": "Column",
"ImpactDetail": "Object dbo.sysssislog.databytes uses deprecated data type TEXT, IMAGE or NTEXT which will be discontinued for future versions of SQL Server. For more details, please see: Line 12, Column 5.",
"SuggestedFixes": []
}
]
},
{
"CompatibilityLevel": "CompatLevel150",
"Category": "Compatibility",
"Severity": "Warning",
"ChangeCategory": "Information",
"RuleId": "Microsoft.Rules.Data.Upgrade.UR00306",
"Title": "Deprecated data types TEXT, IMAGE or NTEXT",
"Impact": "These data types are checked as deprecated. In some cases, using TEXT, IMAGE or NTEXT might harm performance.",
"Recommendation": "Deprecated data types are marked to be discontinued on next versions of SQL Server, should use new data types such as: (varchar(max), nvarchar(max), varbinary(max) and etc.)",
"MoreInfo": "ntext, text, and image (Transact-SQL) (https://go.microsoft.com/fwlink/?LinkId=798558)",
"ImpactedObjects": [
{
"Name": "dbo.sysssislog.databytes",
"ObjectType": "Column",
"ImpactDetail": "Object dbo.sysssislog.databytes uses deprecated data type TEXT, IMAGE or NTEXT which will be discontinued for future versions of SQL Server. For more details, please see: Line 12, Column 5.",
"SuggestedFixes
Author: user
continue
Author: ChatGPT
```sql
: []
}
]
}
],
"ServerEdition": "Enterprise Edition (64-bit)"
}
],
"ServerInstances": [
{
"ServerName": "S1WDVSQLMBI1.us1.autonation.com",
"Version": "14.0.3465.1",
"Status": "Completed",
"AssessmentRecommendations": []
}
],
"SourcePlatform": "SqlOnPrem",
"Status": "Completed",
"StartedOn": "2024-05-17T16:52:09.4679632+00:00",
"EndedOn": "2024-05-17T17:02:06.600904+00:00",
"EvaluateFeatureRecommendations": false,
"EvaluateCompatibilityIssues": true,
"EvaluateFeatureParity": false,
"TargetPlatform": "SqlServerWindows2019",
"DmaVersion": {}
}');
-- Step 2: Extract the Databases array
DECLARE @json NVARCHAR(MAX) = (SELECT TheValue FROM #json);
DECLARE @databases NVARCHAR(MAX) = JSON_VALUE(@json, '$.Databases');
-- Step 3: Extract the AssessmentRecommendations
SELECT
ServerName,
Name AS DatabaseName,
CompatibilityLevel,
Category,
Severity,
ChangeCategory,
RuleId,
Title,
Impact,
Recommendation,
MoreInfo,
ImpactedObjects
FROM
OPENJSON(@databases) WITH (
ServerName VARCHAR(100),
Name VARCHAR(100),
CompatibilityLevel VARCHAR(20),
AssessmentRecommendations NVARCHAR(MAX) AS JSON
) AS databases
OUTER APPLY OPENJSON(databases.AssessmentRecommendations) WITH (
CompatibilityLevel VARCHAR(20),
Category VARCHAR(100),
Severity VARCHAR(100),
ChangeCategory VARCHAR(100),
RuleId VARCHAR(100),
Title VARCHAR(255),
Impact NVARCHAR(MAX),
Recommendation NVARCHAR(MAX),
MoreInfo NVARCHAR(MAX),
ImpactedObjects NVARCHAR(MAX) AS JSON
) AS recommendations;
-- Clean up
DROP TABLE #json;
```
This script will extract the relevant details from the `AssessmentRecommendations` array within the `Databases` array. Adjust the data types and column sizes as needed based on your actual data.