Skip to content

Commit c201e02

Browse files
smpetersgithubsmpetersgithub
authored
Add files via upload
1 parent c13d9b0 commit c201e02

32 files changed

Lines changed: 2695 additions & 0 deletions

‎Database Articles/Database Dependencies/SQL Scripts/01_Cross_Database_Dependencies.sql‎

Lines changed: 102 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,102 @@
1+
-------------------------------------------------------
2+
USE foo;
3+
GO
4+
5+
DROP TABLE IF EXISTS dbo.tbl_example_01;
6+
7+
-------------------------------------------------------
8+
USE bar
9+
GO
10+
11+
DROP VIEW IF EXISTS dbo.vw_example_01;
12+
GO
13+
14+
-------------------------------------------------------
15+
-------------------------------------------------------
16+
-------------------------------------------------------
17+
USE foo;
18+
GO
19+
20+
CREATE TABLE dbo.tbl_example_01
21+
(
22+
OrderID INT,
23+
ProductID INT,
24+
Quantity INT,
25+
UnitPrice MONEY
26+
);
27+
GO
28+
29+
-------------------------------------------------------
30+
USE bar
31+
GO
32+
33+
CREATE VIEW dbo.vw_example_01 AS
34+
SELECT ProductID,
35+
SUM(Quantity) AS QuantityOrdered
36+
FROM foo.dbo.tbl_example_01
37+
GROUP BY ProductID;
38+
GO
39+
40+
-------------------------------------------------------
41+
-------------------------------------------------------
42+
-------------------------------------------------------
43+
USE foo;
44+
GO
45+
46+
DECLARE @vTruncate SMALLINT = 0;
47+
IF @vTruncate = 1
48+
BEGIN
49+
TRUNCATE TABLE foo.dbo.sql_expression_dependencies;
50+
END;
51+
GO
52+
-------------------------------------------------------
53+
USE foo;
54+
GO
55+
56+
INSERT INTO foo.dbo.sql_expression_dependencies
57+
(database_name, example_number, referencing_object_type, referencing_entity_name, referencing_id, referencing_minor_id, referencing_class, referencing_class_desc, is_schema_bound_reference, referenced_class, referenced_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, referenced_object_type, referenced_id, referenced_minor_id, is_caller_dependent, is_ambiguous)
58+
SELECT 'foo', '01', c.type AS referencing_object_type, c.name AS referencing_entity_name, referencing_id, referencing_minor_id, referencing_class, referencing_class_desc, is_schema_bound_reference, referenced_class, referenced_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, b.type AS referenced_object_type, referenced_id, referenced_minor_id, is_caller_dependent, is_ambiguous
59+
FROM sys.sql_expression_dependencies a LEFT OUTER JOIN
60+
sys.objects b ON a.referenced_id = b.object_id LEFT OUTER JOIN
61+
sys.objects c ON a.referencing_id = c.object_id;
62+
GO
63+
64+
-------------------------------------------------------
65+
USE bar;
66+
GO
67+
68+
INSERT INTO foo.dbo.sql_expression_dependencies
69+
(database_name, example_number, referencing_object_type, referencing_entity_name, referencing_id, referencing_minor_id, referencing_class, referencing_class_desc, is_schema_bound_reference, referenced_class, referenced_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, referenced_object_type, referenced_id, referenced_minor_id, is_caller_dependent, is_ambiguous)
70+
SELECT 'bar', '01', c.type AS referencing_object_type, c.name AS referencing_entity_name, referencing_id, referencing_minor_id, referencing_class, referencing_class_desc, is_schema_bound_reference, referenced_class, referenced_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, b.type AS referenced_object_type, referenced_id, referenced_minor_id, is_caller_dependent, is_ambiguous
71+
72+
FROM sys.sql_expression_dependencies a LEFT OUTER JOIN
73+
sys.objects b ON a.referenced_id = b.object_id LEFT OUTER JOIN
74+
sys.objects c ON a.referencing_id = c.object_id;
75+
GO
76+
77+
78+
-------------------------------------------------------
79+
SELECT * FROM foo.dbo.sql_expression_dependencies ORDER BY example_number;
80+
GO
81+
82+
-------------------------------------------------------
83+
USE foo;
84+
GO
85+
86+
DECLARE @vDropObjects SMALLINT = 0;
87+
IF @vDropObjects = 1
88+
BEGIN
89+
DROP TABLE IF EXISTS dbo.tbl_example_01;
90+
END;
91+
GO
92+
-------------------------------------------------------
93+
USE bar
94+
GO
95+
96+
DECLARE @vDropObjects SMALLINT = 0;
97+
IF @vDropObjects = 1
98+
BEGIN
99+
DROP VIEW IF EXISTS dbo.vw_example_01;
100+
END;
101+
102+
GO

‎Database Articles/Database Dependencies/SQL Scripts/02_Cross_Schema_Dependencies.sql‎

Lines changed: 79 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,79 @@
1+
-------------------------------------------------------
2+
USE foo;
3+
GO
4+
5+
DROP TABLE IF EXISTS schemaA.tbl_example_02_schemaA;
6+
DROP TABLE IF EXISTS dbo.tbl_example_02_dbo;
7+
DROP VIEW IF EXISTS dbo.vw_example_02;
8+
GO
9+
10+
-------------------------------------------------------
11+
-------------------------------------------------------
12+
-------------------------------------------------------
13+
USE foo;
14+
GO
15+
16+
CREATE TABLE schemaA.tbl_example_02_schemaA
17+
(
18+
OrderID INT,
19+
ProductID INT,
20+
Quantity INT,
21+
UnitPrice MONEY
22+
);
23+
GO
24+
25+
CREATE TABLE dbo.tbl_example_02_dbo
26+
(
27+
OrderID INT,
28+
ProductID INT,
29+
Quantity INT,
30+
UnitPrice MONEY
31+
);
32+
GO
33+
34+
CREATE VIEW dbo.vw_example_02 AS
35+
SELECT 1 AS myValue
36+
FROM schemaA.tbl_example_02_schemaA CROSS JOIN
37+
dbo.tbl_example_02_dbo;
38+
GO
39+
40+
-------------------------------------------------------
41+
-------------------------------------------------------
42+
-------------------------------------------------------
43+
USE foo;
44+
GO
45+
46+
DECLARE @vTruncate SMALLINT = 0;
47+
IF @vTruncate = 1
48+
BEGIN
49+
TRUNCATE TABLE foo.dbo.sql_expression_dependencies;
50+
END;
51+
GO
52+
-------------------------------------------------------
53+
USE foo;
54+
GO
55+
56+
INSERT INTO foo.dbo.sql_expression_dependencies
57+
(database_name, example_number, referencing_object_type, referencing_entity_name, referencing_id, referencing_minor_id, referencing_class, referencing_class_desc, is_schema_bound_reference, referenced_class, referenced_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, referenced_object_type, referenced_id, referenced_minor_id, is_caller_dependent, is_ambiguous)
58+
SELECT 'foo', '02', c.type AS referencing_object_type, c.name AS referencing_entity_name, referencing_id, referencing_minor_id, referencing_class, referencing_class_desc, is_schema_bound_reference, referenced_class, referenced_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, b.type AS referenced_object_type, referenced_id, referenced_minor_id, is_caller_dependent, is_ambiguous
59+
FROM sys.sql_expression_dependencies a LEFT OUTER JOIN
60+
sys.objects b ON a.referenced_id = b.object_id LEFT OUTER JOIN
61+
sys.objects c ON a.referencing_id = c.object_id;
62+
GO
63+
64+
-------------------------------------------------------
65+
SELECT * FROM foo.dbo.sql_expression_dependencies ORDER BY example_number;
66+
GO
67+
68+
-------------------------------------------------------
69+
USE foo;
70+
GO
71+
72+
DECLARE @vDropObjects SMALLINT = 0;
73+
IF @vDropObjects = 1
74+
BEGIN
75+
DROP TABLE IF EXISTS schemaA.tbl_example_02_schemaA;
76+
DROP TABLE IF EXISTS dbo.tbl_example_02_dbo;
77+
DROP VIEW IF EXISTS dbo.vw_example_02;
78+
END;
79+
GO

‎Database Articles/Database Dependencies/SQL Scripts/03_Invalid_Stored_Procedures.sql‎

Lines changed: 57 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,57 @@
1+
---------------------------------------------
2+
USE foo;
3+
GO
4+
5+
DROP PROCEDURE IF EXISTS dbo.sp_example_03;
6+
GO
7+
---------------------------------------------
8+
---------------------------------------------
9+
---------------------------------------------
10+
USE foo;
11+
GO
12+
13+
CREATE PROCEDURE dbo.sp_example_03 AS
14+
BEGIN
15+
SELECT *
16+
FROM dbo.tbl_does_not_exist_example_03;
17+
END;
18+
GO
19+
20+
-------------------------------------------------------
21+
-------------------------------------------------------
22+
-------------------------------------------------------
23+
USE foo;
24+
GO
25+
26+
DECLARE @vTruncate SMALLINT = 0;
27+
IF @vTruncate = 1
28+
BEGIN
29+
TRUNCATE TABLE foo.dbo.sql_expression_dependencies;
30+
END;
31+
GO
32+
-------------------------------------------------------
33+
USE foo;
34+
GO
35+
36+
INSERT INTO foo.dbo.sql_expression_dependencies
37+
(database_name, example_number, referencing_object_type, referencing_entity_name, referencing_id, referencing_minor_id, referencing_class, referencing_class_desc, is_schema_bound_reference, referenced_class, referenced_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, referenced_object_type, referenced_id, referenced_minor_id, is_caller_dependent, is_ambiguous)
38+
SELECT 'foo', '03', c.type AS referencing_object_type, c.name AS referencing_entity_name, referencing_id, referencing_minor_id, referencing_class, referencing_class_desc, is_schema_bound_reference, referenced_class, referenced_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, b.type AS referenced_object_type, referenced_id, referenced_minor_id, is_caller_dependent, is_ambiguous
39+
FROM sys.sql_expression_dependencies a LEFT OUTER JOIN
40+
sys.objects b ON a.referenced_id = b.object_id LEFT OUTER JOIN
41+
sys.objects c ON a.referencing_id = c.object_id;
42+
GO
43+
44+
-------------------------------------------------------
45+
SELECT * FROM foo.dbo.sql_expression_dependencies ORDER BY example_number;
46+
GO
47+
48+
-------------------------------------------------------
49+
USE foo;
50+
GO
51+
52+
DECLARE @vDropObjects SMALLINT = 0;
53+
IF @vDropObjects = 1
54+
BEGIN
55+
DROP PROCEDURE IF EXISTS dbo.sp_example_03;
56+
END;
57+
GO

‎Database Articles/Database Dependencies/SQL Scripts/04_Numbered_Stored_Procedures.sql‎

Lines changed: 86 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,86 @@
1+
---------------------------------------------
2+
USE foo;
3+
GO
4+
5+
DROP TABLE IF EXISTS dbo.tbl_example_04_a;
6+
DROP TABLE IF EXISTS dbo.tbl_example_04_b;
7+
DROP PROCEDURE IF EXISTS dbo.sp_example_04;
8+
GO
9+
---------------------------------------------
10+
---------------------------------------------
11+
---------------------------------------------
12+
USE foo;
13+
GO
14+
15+
CREATE TABLE dbo.tbl_example_04_a
16+
(
17+
OrderID INT,
18+
ProductID INT,
19+
Quantity INT,
20+
UnitPrice MONEY
21+
);
22+
GO
23+
24+
CREATE TABLE dbo.tbl_example_04_b
25+
(
26+
OrderID INT,
27+
ProductID INT,
28+
Quantity INT,
29+
UnitPrice MONEY
30+
);
31+
GO
32+
33+
CREATE PROCEDURE dbo.sp_example_04;1 AS
34+
BEGIN
35+
SELECT *
36+
FROM dbo.tbl_example_04_a;
37+
END;
38+
GO
39+
40+
CREATE PROCEDURE dbo.sp_example_04;2 AS
41+
BEGIN
42+
SELECT *
43+
FROM dbo.tbl_example_04_b;
44+
END;
45+
GO
46+
47+
-------------------------------------------------------
48+
-------------------------------------------------------
49+
-------------------------------------------------------
50+
USE foo;
51+
GO
52+
53+
DECLARE @vTruncate SMALLINT = 0;
54+
IF @vTruncate = 1
55+
BEGIN
56+
TRUNCATE TABLE foo.dbo.sql_expression_dependencies;
57+
END;
58+
GO
59+
-------------------------------------------------------
60+
USE foo;
61+
GO
62+
63+
INSERT INTO foo.dbo.sql_expression_dependencies
64+
(database_name, example_number, referencing_object_type, referencing_entity_name, referencing_id, referencing_minor_id, referencing_class, referencing_class_desc, is_schema_bound_reference, referenced_class, referenced_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, referenced_object_type, referenced_id, referenced_minor_id, is_caller_dependent, is_ambiguous)
65+
SELECT 'foo', '04', c.type AS referencing_object_type, c.name AS referencing_entity_name, referencing_id, referencing_minor_id, referencing_class, referencing_class_desc, is_schema_bound_reference, referenced_class, referenced_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, b.type AS referenced_object_type, referenced_id, referenced_minor_id, is_caller_dependent, is_ambiguous
66+
FROM sys.sql_expression_dependencies a LEFT OUTER JOIN
67+
sys.objects b ON a.referenced_id = b.object_id LEFT OUTER JOIN
68+
sys.objects c ON a.referencing_id = c.object_id;
69+
GO
70+
71+
-------------------------------------------------------
72+
SELECT * FROM foo.dbo.sql_expression_dependencies ORDER BY example_number;
73+
GO
74+
75+
---------------------------------------------
76+
USE foo;
77+
GO
78+
79+
DECLARE @vDropObjects SMALLINT = 0;
80+
IF @vDropObjects = 1
81+
BEGIN
82+
DROP TABLE IF EXISTS dbo.tbl_example_04_a;
83+
DROP TABLE IF EXISTS dbo.tbl_example_04_b;
84+
DROP PROCEDURE IF EXISTS dbo.sp_example_04;
85+
END;
86+
GO

0 commit comments

Comments
 (0)