Skip to content

Commit 684d23f

Browse files
ktaranovktaranov
committed
Add new function and script
 - udf_JSONHierarchy - Compare_2_tables_by_column_names
1 parent 7681fc0 commit 684d23f

3 files changed

Lines changed: 177 additions & 2 deletions

File tree

‎Scripts/Compare_2_tables_by_column_names.sql‎

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,31 @@
1+
/*
2+
Author: Shane O'Neill
3+
Source link: https://nocolumnname.wordpress.com/2017/04/20/stop-hammer-pivot/
4+
*/
5+
6+
SELECT Pivot1.ColumnName,
7+
Pivot1.[dbo.DifferenceTable01],
8+
Pivot1.[dbo.DifferenceTable02],
9+
CASE WHEN [dbo.DifferenceTable01] = 1 AND [dbo.DifferenceTable02] = 1
10+
THEN 'Both'
11+
WHEN [dbo.DifferenceTable01] = 1 AND [dbo.DifferenceTable02] IS NULL
12+
THEN 'Table 1 only'
13+
WHEN [dbo.DifferenceTable01] IS NULL AND [dbo.DifferenceTable02] = 1
14+
THEN 'Table 2 only'
15+
ELSE 'Eh...this should not really happen'
16+
END AS HumanReadableFormat
17+
FROM ( SELECT
18+
c.[name] AS ColumnName,
19+
tb.TableName,
20+
1 AS ColumnExists
21+
FROM sys.columns AS c
22+
RIGHT JOIN ( VALUES
23+
(OBJECT_ID(N'dbo.DifferenceTable01', N'U'), 'dbo.DifferenceTable01'),
24+
(OBJECT_ID(N'dbo.DifferenceTable02', N'U'), 'dbo.DifferenceTable02')
25+
) AS tb (ObjectID, TableName)
26+
ON c.object_id = tb.ObjectID
27+
) AS UnPivotedColumns
28+
PIVOT (
29+
MAX(ColumnExists) FOR TableName IN ([dbo.DifferenceTable01], [dbo.DifferenceTable02])
30+
) AS Pivot1
31+
ORDER BY Pivot1.ColumnName ASC;

‎User_Defined_Function/udf_GetResourceName.sql‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,8 +8,10 @@ END;
88
GO
99

1010

11-
CREATE FUNCTION [dbo].[udf_GetResourceName] ( @waitResource AS nvarchar(128)
12-
, @paramName as sysname =NULL )
11+
CREATE FUNCTION dbo.udf_GetResourceName(
12+
@waitResource AS nvarchar(128)
13+
, @paramName as sysname =NULL
14+
)
1315
RETURNS nvarchar(max) --we will return t-sql
1416
AS
1517
/*

‎User_Defined_Function/udf_JSONHierarchy.sql‎

Lines changed: 142 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,142 @@
1+
IF (SELECT Compatibility_level FROM sys.databases WHERE name LIKE Db_Name())<130
2+
ALTER DATABASE MyDatabase SET COMPATIBILITY_LEVEL = 130;
3+
4+
IF EXISTS (SELECT * FROM sys.types WHERE name LIKE 'Hierarchy')
5+
SET NOEXEC On;
6+
GO
7+
CREATE TYPE dbo.Hierarchy AS TABLE
8+
/*Markup languages such as JSON and XML all represent object data as hierarchies. Although it looks very different to the entity-relational model, it isn't. It is rather more a different perspective on the same model. The first trick is to represent it as a Adjacency list hierarchy in a table, and then use the contents of this table to update the database. This Adjacency list is really the Database equivalent of any of the nested data structures that are used for the interchange of serialized information with the application, and can be used to create XML, OSX Property lists, Python nested structures or YAML as easily as JSON.
9+
Adjacency list tables have the same structure whatever the data in them. This means that you can define a single Table-Valued Type and pass data structures around between stored procedures. However, they are best held at arms-length from the data, since they are not relational tables, but something more like the dreaded EAV (Entity-Attribute-Value) tables. Converting the data from its Hierarchical table form will be different for each application, but is easy with a CTE. You can, alternatively, convert the hierarchical table into XML and interrogate that with XQuery
10+
*/
11+
(
12+
element_id INT primary key, /* internal surrogate primary key gives the order of parsing and the list order */
13+
sequenceNo [int] NULL, /* the place in the sequence for the element */
14+
parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */
15+
Object_ID INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
16+
NAME NVARCHAR(2000),/* the name of the object, null if it hasn't got one */
17+
StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */
18+
ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/
19+
)
20+
GO
21+
SET NOEXEC OFF
22+
GO
23+
24+
IF Object_Id('dbo.udf_JSONHierarchy', 'TF') IS NOT NULL
25+
CREATE FUNCTION dbo.udf_JSONHierarchy
26+
GO
27+
28+
ALTER FUNCTION dbo.udf_JSONHierarchy
29+
(
30+
@JSONData VARCHAR(MAX),
31+
@Parent_object_ID INT = NULL,
32+
@MaxObject_id INT = 0,
33+
@type INT = null
34+
)
35+
/*
36+
Author: Phil Factor
37+
Source link: https://www.red-gate.com/simple-talk/blogs/consuming-hierarchical-json-documents-sql-server-using-openjson/
38+
Description: Consuming hierarchical JSON documents in SQL Server using OpenJSON
39+
40+
SELECT * FROM dbo.JSONHierarchy('{ "Person":
41+
{
42+
"firstName": "John",
43+
"lastName": "Smith",
44+
"age": 25,
45+
"Address":
46+
{
47+
"streetAddress":"21 2nd Street",
48+
"city":"New York",
49+
"state":"NY",
50+
"postalCode":"10021"
51+
},
52+
"PhoneNumbers":
53+
{
54+
"home":"212 555-1234",
55+
"fax":"646 555-4567"
56+
}
57+
}
58+
}'
59+
,DEFAULT,DEFAULT,DEFAULT)
60+
*/
61+
RETURNS @ReturnTable TABLE
62+
(
63+
Element_ID INT IDENTITY(1, 1) PRIMARY KEY, /* internal surrogate primary key gives the order of parsing and the list order */
64+
SequenceNo INT NULL, /* the sequence number in a list */
65+
Parent_ID INT, /* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */
66+
Object_ID INT, /* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
67+
Name NVARCHAR(2000), /* the name of the object */
68+
StringValue NVARCHAR(MAX) NOT NULL, /*the string representation of the value of the element. */
69+
ValueType VARCHAR(10) NOT NULL /* the declared type of the value represented as a string in StringValue*/
70+
)
71+
AS
72+
BEGIN
73+
--the types of JSON
74+
DECLARE @null INT =
75+
0, @string INT = 1, @int INT = 2, @boolean INT = 3, @array INT = 4, @object INT = 5;
76+
77+
DECLARE @OpenJSONData TABLE
78+
(
79+
sequence INT IDENTITY(1, 1),
80+
[key] VARCHAR(200),
81+
Value VARCHAR(MAX),
82+
type INT
83+
);
84+
85+
DECLARE @key VARCHAR(200), @Value VARCHAR(MAX), @Thetype INT, @ii INT, @iiMax INT,
86+
@NewObject INT, @firstchar CHAR(1);
87+
88+
INSERT INTO @OpenJSONData
89+
([key], Value, type)
90+
SELECT [Key], Value, Type FROM OpenJson(@JSONData);
91+
SELECT @ii = 1, @iiMax = Scope_Identity()
92+
SELECT @Firstchar= --the first character to see if it is an object or an array
93+
Substring(@JSONData,PatIndex('%[^'+CHAR(0)+'- '+CHAR(160)+']%',' '+@JSONData+'!' collate SQL_Latin1_General_CP850_Bin)-1,1)
94+
IF @type IS NULL AND @firstchar IN ('[','{')
95+
begin
96+
INSERT INTO @returnTable
97+
(SequenceNo,Parent_ID,Object_ID,Name,StringValue,ValueType)
98+
SELECT 1,NULL,1,'-','',
99+
CASE @firstchar WHEN '[' THEN 'array' ELSE 'object' END
100+
SELECT @type=CASE @firstchar WHEN '[' THEN @array ELSE @object END,
101+
@Parent_object_ID = 1, @MaxObject_id=Coalesce(@MaxObject_id, 1) + 1;
102+
END
103+
WHILE(@ii <= @iiMax)
104+
BEGIN
105+
--OpenJSON renames list items with 0-nn which confuses the consumers of the table
106+
SELECT @key = CASE WHEN [key] LIKE '[0-9]%' THEN NULL ELSE [key] end , @Value = Value, @Thetype = type
107+
FROM @OpenJSONData
108+
WHERE sequence = @ii;
109+
110+
IF @Thetype IN (@array, @object) --if we have been returned an array or object
111+
BEGIN
112+
SELECT @MaxObject_id = Coalesce(@MaxObject_id, 1) + 1;
113+
--just in case we have an object or array returned
114+
INSERT INTO @ReturnTable --record the object itself
115+
(SequenceNo, Parent_ID, Object_ID, Name, StringValue, ValueType)
116+
SELECT @ii, @Parent_object_ID, @MaxObject_id, @key, '',
117+
CASE @Thetype WHEN @array THEN 'array' ELSE 'object' END;
118+
119+
INSERT INTO @ReturnTable --and return all its children
120+
(SequenceNo, Parent_ID, Object_ID, [Name], StringValue, ValueType)
121+
SELECT SequenceNo, Parent_ID, Object_ID,
122+
[Name],
123+
StringValue,
124+
ValueType
125+
FROM dbo.udf_JSONHierarchy(@Value, @MaxObject_id, @MaxObject_id, @type);
126+
SELECT @MaxObject_id=Max(Object_id)+1 FROM @ReturnTable
127+
END;
128+
ELSE
129+
INSERT INTO @ReturnTable
130+
(SequenceNo, Parent_ID, Object_ID, Name, StringValue, ValueType)
131+
SELECT @ii, @Parent_object_ID, NULL, @key, @Value,
132+
CASE @Thetype WHEN @string THEN 'string'
133+
WHEN @null THEN 'null'
134+
WHEN @int THEN 'int'
135+
WHEN @boolean THEN 'boolean' ELSE 'int' END;
136+
137+
SELECT @ii = @ii + 1;
138+
END;
139+
140+
RETURN;
141+
END;
142+
GO

0 commit comments

Comments
 (0)