Skip to content

Commit d64157b

Browse files
smpetersgithubsmpetersgithub
authored
Add files via upload
1 parent 0ad11eb commit d64157b

32 files changed

Lines changed: 433 additions & 200 deletions

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

Lines changed: 20 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -43,7 +43,7 @@ GO
4343
USE foo;
4444
GO
4545

46-
DECLARE @vTruncate SMALLINT = 1;
46+
DECLARE @vTruncate SMALLINT = 0;
4747
IF @vTruncate = 1
4848
BEGIN
4949
TRUNCATE TABLE foo.dbo.sql_expression_dependencies;
@@ -54,26 +54,38 @@ USE foo;
5454
GO
5555

5656
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
57+
(example_number, referencing_object_type, referencing_server_name, referencing_database_name, referencing_schema_name, 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 '01', COALESCE(c.type, d.type, e.type) AS referencing_object_type, @@SERVERNAME, DB_NAME(), SCHEMA_NAME(c.schema_id), COALESCE(c.name, d.name, e.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
5959
FROM sys.sql_expression_dependencies a LEFT OUTER JOIN
6060
sys.objects b ON a.referenced_id = b.object_id LEFT OUTER JOIN
61-
sys.objects c ON a.referencing_id = c.object_id;
61+
sys.objects c ON a.referencing_id = c.object_id LEFT OUTER JOIN
62+
sys.triggers d ON a.referencing_id = d.object_id LEFT OUTER JOIN
63+
sys.server_triggers e ON a.referencing_id = e.object_id;
64+
GO
65+
66+
INSERT INTO foo.dbo.system_objects (table_name, object_id, server_name, database_name, schema_name, name, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published)
67+
SELECT 'sys.objects', object_id, @@SERVERNAME, DB_NAME(), SCHEMA_NAME(schema_id), name, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
68+
FROM sys.objects
6269
GO
6370

6471
-------------------------------------------------------
6572
USE bar;
6673
GO
6774

6875
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-
76+
(example_number, referencing_object_type, referencing_server_name, referencing_database_name, referencing_schema_name, 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)
77+
SELECT '01', COALESCE(c.type, d.type, e.type) AS referencing_object_type, @@SERVERNAME, DB_NAME(), SCHEMA_NAME(c.schema_id), COALESCE(c.name, d.name, e.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
7278
FROM sys.sql_expression_dependencies a LEFT OUTER JOIN
7379
sys.objects b ON a.referenced_id = b.object_id LEFT OUTER JOIN
74-
sys.objects c ON a.referencing_id = c.object_id;
80+
sys.objects c ON a.referencing_id = c.object_id LEFT OUTER JOIN
81+
sys.triggers d ON a.referencing_id = d.object_id LEFT OUTER JOIN
82+
sys.server_triggers e ON a.referencing_id = e.object_id;
7583
GO
7684

85+
INSERT INTO foo.dbo.system_objects (table_name, object_id, server_name, database_name, schema_name, name, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published)
86+
SELECT 'sys.objects', object_id, @@SERVERNAME, DB_NAME(), SCHEMA_NAME(schema_id), name, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
87+
FROM sys.objects
88+
GO
7789

7890
-------------------------------------------------------
7991
SELECT * FROM foo.dbo.sql_expression_dependencies ORDER BY example_number;

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

Lines changed: 11 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -43,7 +43,7 @@ GO
4343
USE foo;
4444
GO
4545

46-
DECLARE @vTruncate SMALLINT = 1;
46+
DECLARE @vTruncate SMALLINT = 0;
4747
IF @vTruncate = 1
4848
BEGIN
4949
TRUNCATE TABLE foo.dbo.sql_expression_dependencies;
@@ -54,11 +54,18 @@ USE foo;
5454
GO
5555

5656
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
57+
(example_number, referencing_object_type, referencing_server_name, referencing_database_name, referencing_schema_name, 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 '02', COALESCE(c.type, d.type, e.type) AS referencing_object_type, @@SERVERNAME, DB_NAME(), SCHEMA_NAME(c.schema_id), COALESCE(c.name, d.name, e.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
5959
FROM sys.sql_expression_dependencies a LEFT OUTER JOIN
6060
sys.objects b ON a.referenced_id = b.object_id LEFT OUTER JOIN
61-
sys.objects c ON a.referencing_id = c.object_id;
61+
sys.objects c ON a.referencing_id = c.object_id LEFT OUTER JOIN
62+
sys.triggers d ON a.referencing_id = d.object_id LEFT OUTER JOIN
63+
sys.server_triggers e ON a.referencing_id = e.object_id;
64+
GO
65+
66+
INSERT INTO foo.dbo.system_objects (table_name, object_id, server_name, database_name, schema_name, name, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published)
67+
SELECT 'sys.objects', object_id, @@SERVERNAME, DB_NAME(), SCHEMA_NAME(schema_id), name, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
68+
FROM sys.objects
6269
GO
6370

6471
-------------------------------------------------------

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

Lines changed: 11 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -23,7 +23,7 @@ GO
2323
USE foo;
2424
GO
2525

26-
DECLARE @vTruncate SMALLINT = 1;
26+
DECLARE @vTruncate SMALLINT = 0;
2727
IF @vTruncate = 1
2828
BEGIN
2929
TRUNCATE TABLE foo.dbo.sql_expression_dependencies;
@@ -34,11 +34,18 @@ USE foo;
3434
GO
3535

3636
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
37+
(example_number, referencing_object_type, referencing_server_name, referencing_database_name, referencing_schema_name, 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 '03', COALESCE(c.type, d.type, e.type) AS referencing_object_type, @@SERVERNAME, DB_NAME(), SCHEMA_NAME(c.schema_id), COALESCE(c.name, d.name, e.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
3939
FROM sys.sql_expression_dependencies a LEFT OUTER JOIN
4040
sys.objects b ON a.referenced_id = b.object_id LEFT OUTER JOIN
41-
sys.objects c ON a.referencing_id = c.object_id;
41+
sys.objects c ON a.referencing_id = c.object_id LEFT OUTER JOIN
42+
sys.triggers d ON a.referencing_id = d.object_id LEFT OUTER JOIN
43+
sys.server_triggers e ON a.referencing_id = e.object_id;
44+
GO
45+
46+
INSERT INTO foo.dbo.system_objects (table_name, object_id, server_name, database_name, schema_name, name, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published)
47+
SELECT 'sys.objects', object_id, @@SERVERNAME, DB_NAME(), SCHEMA_NAME(schema_id), name, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
48+
FROM sys.objects
4249
GO
4350

4451
-------------------------------------------------------

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

Lines changed: 11 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -50,7 +50,7 @@ GO
5050
USE foo;
5151
GO
5252

53-
DECLARE @vTruncate SMALLINT = 1;
53+
DECLARE @vTruncate SMALLINT = 0;
5454
IF @vTruncate = 1
5555
BEGIN
5656
TRUNCATE TABLE foo.dbo.sql_expression_dependencies;
@@ -61,11 +61,18 @@ USE foo;
6161
GO
6262

6363
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
64+
(example_number, referencing_object_type, referencing_server_name, referencing_database_name, referencing_schema_name, 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 '04', COALESCE(c.type, d.type, e.type) AS referencing_object_type, @@SERVERNAME, DB_NAME(), SCHEMA_NAME(c.schema_id), COALESCE(c.name, d.name, e.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
6666
FROM sys.sql_expression_dependencies a LEFT OUTER JOIN
6767
sys.objects b ON a.referenced_id = b.object_id LEFT OUTER JOIN
68-
sys.objects c ON a.referencing_id = c.object_id;
68+
sys.objects c ON a.referencing_id = c.object_id LEFT OUTER JOIN
69+
sys.triggers d ON a.referencing_id = d.object_id LEFT OUTER JOIN
70+
sys.server_triggers e ON a.referencing_id = e.object_id;
71+
GO
72+
73+
INSERT INTO foo.dbo.system_objects (table_name, object_id, server_name, database_name, schema_name, name, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published)
74+
SELECT 'sys.objects', object_id, @@SERVERNAME, DB_NAME(), SCHEMA_NAME(schema_id), name, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
75+
FROM sys.objects
6976
GO
7077

7178
-------------------------------------------------------

‎Database Articles/Database Dependencies/SQL Scripts/05_Ambiguous_References.sql‎

Lines changed: 11 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -29,10 +29,8 @@ GO
2929

3030
CREATE PROCEDURE dbo.sp_example_05 (@inputInt INT) AS
3131
BEGIN
32-
3332
SELECT dbo.fn_example_05(tbl_example_05.OrderID)
3433
FROM dbo.tbl_example_05;
35-
3634
END;
3735
GO
3836

@@ -42,7 +40,7 @@ GO
4240
USE foo;
4341
GO
4442

45-
DECLARE @vTruncate SMALLINT = 1;
43+
DECLARE @vTruncate SMALLINT = 0;
4644
IF @vTruncate = 1
4745
BEGIN
4846
TRUNCATE TABLE foo.dbo.sql_expression_dependencies;
@@ -53,11 +51,18 @@ USE foo;
5351
GO
5452

5553
INSERT INTO foo.dbo.sql_expression_dependencies
56-
(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)
57-
SELECT 'foo', '05', 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
54+
(example_number, referencing_object_type, referencing_server_name, referencing_database_name, referencing_schema_name, 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)
55+
SELECT '05', COALESCE(c.type, d.type, e.type) AS referencing_object_type, @@SERVERNAME, DB_NAME(), SCHEMA_NAME(c.schema_id), COALESCE(c.name, d.name, e.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
5856
FROM sys.sql_expression_dependencies a LEFT OUTER JOIN
5957
sys.objects b ON a.referenced_id = b.object_id LEFT OUTER JOIN
60-
sys.objects c ON a.referencing_id = c.object_id;
58+
sys.objects c ON a.referencing_id = c.object_id LEFT OUTER JOIN
59+
sys.triggers d ON a.referencing_id = d.object_id LEFT OUTER JOIN
60+
sys.server_triggers e ON a.referencing_id = e.object_id;
61+
GO
62+
63+
INSERT INTO foo.dbo.system_objects (table_name, object_id, server_name, database_name, schema_name, name, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published)
64+
SELECT 'sys.objects', object_id, @@SERVERNAME, DB_NAME(), SCHEMA_NAME(schema_id), name, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
65+
FROM sys.objects
6166
GO
6267

6368
-------------------------------------------------------

0 commit comments

Comments
 (0)