Jasne i ciemne strony baz danych

… w szczególności MS SQL Server ;-)

Posty oznaczone jako ‘obiekty’

SQL Server – Zależności między obiektami w bazach danych

Posted by C3PO w dniu 25 Lipiec 2010

Śledzenie zależności między obiektami w bazach danych w SQL Server to ostatnio częsty temat na forum portalu WSS.pl (np. patrz tu lub tu). Udało mi się w ramach tych wątków popełnić jedno drobne niedomówienie sprawnie wychwycone i sprostowane przez Marka Adamczuka (w końcu SQL Server MVP, więc nic dziwnego, że prawdziwą prawdę znał / zbadał i opisał:-)). Dlatego postanowiłem napisać notkę ku pamięci swojej i czytających mojego bloga poświęconą właśnie wspomnianym zależnościom między obiektami – nazwijmy je w skrócie “zależnościami”.

SQL Server 2000

Do badania zależności na SQL Server 2000 wykorzystajmy taki skrypt:

USE tempdb;
GO
-- sprzątamy
IF OBJECT_ID('dbo.p1', 'P') IS NOT NULL
  DROP PROC dbo.p1;
IF OBJECT_ID('dbo.p2', 'P') IS NOT NULL
  DROP PROC dbo.p2; 
IF OBJECT_ID('dbo.v1', 'V') IS NOT NULL
  DROP VIEW dbo.v1;
IF OBJECT_ID('dbo.t1', 'U') IS NOT NULL
  DROP TABLE dbo.t1;
GO
-- tworzymy obiekty
CREATE TABLE dbo.t1 (c1 int NOT NULL, c2 int NOT NULL);
GO
CREATE VIEW dbo.v1
AS
SELECT * FROM dbo.t1
GO
CREATE PROC dbo.p2
AS
GO
CREATE PROC dbo.p1
AS
SELECT * FROM dbo.v1;
EXEC dbo.p2;
GO
-- sprawdzamy zależności (1)
SELECT
  OBJECT_NAME(id) AS obj,
  OBJECT_NAME(depid) AS depobj,
  *
FROM dbo.sysdepends
WHERE id IN (OBJECT_ID('dbo.p1'), OBJECT_ID('dbo.v1'));
GO
-- zmieniamy widok
ALTER VIEW dbo.v1
AS
SELECT c1 FROM dbo.t1
GO
ALTER PROC dbo.p2
AS
-- zmiana
GO
-- sprawdzamy zależności (2)
SELECT
  OBJECT_NAME(id) AS obj,
  OBJECT_NAME(depid) AS depobj,
  *
FROM dbo.sysdepends
WHERE id IN (OBJECT_ID('dbo.p1'), OBJECT_ID('dbo.v1'));
GO
-- sprzątamy
IF OBJECT_ID('dbo.p1', 'P') IS NOT NULL
  DROP PROC dbo.p1;
IF OBJECT_ID('dbo.p2', 'P') IS NOT NULL
  DROP PROC dbo.p2; 
IF OBJECT_ID('dbo.v1', 'V') IS NOT NULL
  DROP VIEW dbo.v1;
IF OBJECT_ID('dbo.t1', 'U') IS NOT NULL
  DROP TABLE dbo.t1;
GO
-- tworzymy obiekty
CREATE TABLE dbo.t1 (c1 int NOT NULL, c2 int NOT NULL);
GO
CREATE VIEW dbo.v1
AS
SELECT * FROM dbo.t1
GO
CREATE PROC dbo.p2
AS
GO
CREATE PROC dbo.p1
AS
SELECT * FROM dbo.v1;
EXEC dbo.p2;
GO
-- usuwamy widok, procedurę i tabelę
DROP VIEW dbo.v1;
DROP PROC dbo.p2; 
DROP TABLE dbo.t1;
GO
CREATE TABLE dbo.t1 (c1 int NOT NULL, c2 int NOT NULL);
GO
CREATE VIEW dbo.v1
AS
SELECT * FROM dbo.t1
GO
CREATE PROC dbo.p2
AS
GO
-- sprawdzamy zależności (3)
SELECT
  OBJECT_NAME(id) AS obj,
  OBJECT_NAME(depid) AS depobj,
  *
FROM dbo.sysdepends
WHERE id IN (OBJECT_ID('dbo.p1'), OBJECT_ID('dbo.v1'));
GO

Komentarz:

  • tworzymy tabelę t1,
  • tworzymy widok v1 używający tabeli t1,
  • tworzymy “pustą” procedurę p2,
  • tworzymy procedurę p1 używającą widoku v1 i procedury p2,
  • zależności między obiektami badamy używając tabeli systemowej dbo.sysdepends,
  • badamy wpływ operacji ALTER wykonanych na widoku v1 i procedurze p2 na zależności,
  • badamy wpływ operacji DROP i CREATE wykonanych na widoku v1, procedurze p2 i tabeli t1 na zależności.

Obserwacje:

  • ALTER na widoku v1 powoduje “zgubienie” zależności między widokiem v1 a procedurą p1 (choć procedura nadal używa widoku),
  • ALTER na procedurze p2 powoduje “zgubienie” zależności między procedurą p2 a procedurą p1 (choć p1 nadal używa p2),
  • DROP i CREATE wykonane na widoku v1 także powodują “zgubienie” zależności między widokiem v1 a procedurą p1 (choć procedura nadal używa widoku),
  • DROP i CREATE wykonane na widoku p2 także powodują “zgubienie” zależności między procedurą p2 a procedurą p1 (choć p1 nadal używa p2).

SQL Server 2005

Do badania zależności na SQL Server 2005 wykorzystajmy taki skrypt:

USE tempdb;
GO
-- sprzątamy
IF OBJECT_ID('dbo.p1', 'P') IS NOT NULL
  DROP PROC dbo.p1;
IF OBJECT_ID('dbo.p2', 'P') IS NOT NULL
  DROP PROC dbo.p2; 
IF OBJECT_ID('dbo.v1', 'V') IS NOT NULL
  DROP VIEW dbo.v1;
IF OBJECT_ID('dbo.t1', 'U') IS NOT NULL
  DROP TABLE dbo.t1;
GO
-- tworzymy obiekty
CREATE TABLE dbo.t1 (c1 int NOT NULL, c2 int NOT NULL);
GO
CREATE VIEW dbo.v1
AS
SELECT * FROM dbo.t1
GO
CREATE PROC dbo.p2
AS
GO
CREATE PROC dbo.p1
AS
SELECT * FROM dbo.v1;
EXEC dbo.p2;
GO
-- sprawdzamy zależności (1)
SELECT
  OBJECT_NAME([object_id]) AS obj,
  OBJECT_NAME(referenced_major_id) AS refobj,
  *
FROM sys.sql_dependencies
WHERE [object_id] IN (OBJECT_ID('dbo.p1'), OBJECT_ID('dbo.v1'));
GO
-- zmieniamy widok i procedurę
ALTER VIEW dbo.v1
AS
SELECT c1 FROM dbo.t1
GO
ALTER PROC dbo.p2
AS
-- zmiana
GO
-- sprawdzamy zależności (2)
SELECT
  OBJECT_NAME([object_id]) AS obj,
  OBJECT_NAME(referenced_major_id) AS refobj,
  *
FROM sys.sql_dependencies
WHERE [object_id] IN (OBJECT_ID('dbo.p1'), OBJECT_ID('dbo.v1'));
GO
-- sprzątamy
IF OBJECT_ID('dbo.p1', 'P') IS NOT NULL
  DROP PROC dbo.p1;
IF OBJECT_ID('dbo.p2', 'P') IS NOT NULL
  DROP PROC dbo.p2; 
IF OBJECT_ID('dbo.v1', 'V') IS NOT NULL
  DROP VIEW dbo.v1;
IF OBJECT_ID('dbo.t1', 'U') IS NOT NULL
  DROP TABLE dbo.t1;
GO
-- tworzymy obiekty
CREATE TABLE dbo.t1 (c1 int NOT NULL, c2 int NOT NULL);
GO
CREATE VIEW dbo.v1
AS
SELECT * FROM dbo.t1
GO
CREATE PROC dbo.p2
AS
GO
CREATE PROC dbo.p1
AS
SELECT * FROM dbo.v1;
EXEC dbo.p2;
GO
-- usuwamy widok i tabelę
DROP VIEW dbo.v1;
DROP PROC dbo.p2;
DROP TABLE dbo.t1;
GO
CREATE TABLE dbo.t1 (c1 int NOT NULL);
GO
CREATE VIEW dbo.v1
AS
SELECT * FROM dbo.t1
GO
CREATE PROC dbo.p2
AS
GO
-- sprawdzamy zależności (3)
SELECT
  OBJECT_NAME([object_id]) AS obj,
  OBJECT_NAME(referenced_major_id) AS refobj,
  *
FROM sys.sql_dependencies
WHERE [object_id] IN (OBJECT_ID('dbo.p1'), OBJECT_ID('dbo.v1'));
GO

Komentarz:

  • tworzymy tabelę t1,
  • tworzymy widok v1 używający tabeli t1,
  • tworzymy “pustą” procedurę p2,
  • tworzymy procedurę p1 używającą widoku v1 i procedury p2,
  • zależności między obiektami badamy używając widoku systemowego sys.sql_dependencies,
  • badamy wpływ operacji ALTER wykonanych na widoku v1 i procedurze p2 na zależności,
  • badamy wpływ operacji DROP i CREATE wykonanych na widoku v1, procedurze p2 i tabeli t1 na zależności.

Obserwacje:

  • ALTER na widoku v1 powoduje “zgubienie” zależności między widokiem v1 a procedurą p1,
  • ALTER na procedurze p2 NIE powoduje “zgubienia” zależności między procedurą p2 a procedurą p1, 
  • DROP i CREATE powodują “zgubienie” zależności między widokiem v1 a procedurą p1 (choć procedura nadal używa widoku).

SQL Server 2008 / 2008 R2

Do badania zależności na SQL Server 2008 / 2008 R2 wykorzystajmy taki skrypt:

USE tempdb;
GO
-- sprzątamy
IF OBJECT_ID('dbo.p1', 'P') IS NOT NULL
  DROP PROC dbo.p1;
IF OBJECT_ID('dbo.p2', 'P') IS NOT NULL
  DROP PROC dbo.p2; 
IF OBJECT_ID('dbo.v1', 'V') IS NOT NULL
  DROP VIEW dbo.v1;
IF OBJECT_ID('dbo.t1', 'U') IS NOT NULL
  DROP TABLE dbo.t1;
GO
-- tworzymy obiekty
CREATE TABLE dbo.t1 (c1 int NOT NULL, c2 int NOT NULL);
GO
CREATE VIEW dbo.v1
AS
SELECT * FROM dbo.t1
GO
CREATE PROC dbo.p2
AS
GO
CREATE PROC dbo.p1
AS
SELECT * FROM dbo.v1;
EXEC dbo.p2;
GO
-- sprawdzamy zależności (1)
SELECT
  OBJECT_NAME([referencing_id]) AS referencing_object,
  *
FROM sys.sql_expression_dependencies
WHERE [referencing_id] IN (OBJECT_ID('dbo.p1'), OBJECT_ID('dbo.v1'));
GO
-- zmieniamy widok i procedurę
ALTER VIEW dbo.v1
AS
SELECT c1 FROM dbo.t1
GO
ALTER PROC dbo.p2
AS
-- zmiana
GO
-- sprawdzamy zależności (2)
SELECT
  OBJECT_NAME([referencing_id]) AS referencing_object,
  *
FROM sys.sql_expression_dependencies
WHERE [referencing_id] IN (OBJECT_ID('dbo.p1'), OBJECT_ID('dbo.v1'));
GO
-- sprzątamy
IF OBJECT_ID('dbo.p1', 'P') IS NOT NULL
  DROP PROC dbo.p1;
IF OBJECT_ID('dbo.p2', 'P') IS NOT NULL
  DROP PROC dbo.p2; 
IF OBJECT_ID('dbo.v1', 'V') IS NOT NULL
  DROP VIEW dbo.v1;
IF OBJECT_ID('dbo.t1', 'U') IS NOT NULL
  DROP TABLE dbo.t1;
GO
-- tworzymy obiekty
CREATE TABLE dbo.t1 (c1 int NOT NULL, c2 int NOT NULL);
GO
CREATE VIEW dbo.v1
AS
SELECT * FROM dbo.t1
GO
CREATE PROC dbo.p2
AS
GO
CREATE PROC dbo.p1
AS
SELECT * FROM dbo.v1;
EXEC dbo.p2;
GO
-- usuwamy widok i tabelę
DROP VIEW dbo.v1;
DROP PROC dbo.p2;
DROP TABLE dbo.t1;
GO
CREATE TABLE dbo.t1 (c1 int NOT NULL);
GO
CREATE VIEW dbo.v1
AS
SELECT * FROM dbo.t1
GO
CREATE PROC dbo.p2
AS
GO
-- sprawdzamy zależności (3)
SELECT
  OBJECT_NAME([referencing_id]) AS referencing_object,
  *
FROM sys.sql_expression_dependencies
WHERE [referencing_id] IN (OBJECT_ID('dbo.p1'), OBJECT_ID('dbo.v1'));
GO

Komentarz:

  • tworzymy tabelę t1,
  • tworzymy widok v1 używający tabeli t1,
  • tworzymy “pustą” procedurę p2,
  • tworzymy procedurę p1 używającą widoku v1 i procedury p2,
  • zależności między obiektami badamy używając widoku systemowego sys.sql_expression_dependencies,
  • badamy wpływ operacji ALTER wykonanych na widoku v1 i procedurze p2 na zależności,
  • badamy wpływ operacji DROP i CREATE wykonanych na widoku v1, procedurze p2 i tabeli t1 na zależności.

Obserwacje:

  • żadna operacja nie spowodowała “zgubienia” zależności między obiektami,
  • w przeciwieństwie do tabeli dbo.sysdepends z SQL Server 2000 i widoku sys.sql_dependencies z SQL Server 2005, widok sys.sql_expression_dependencies nie pokazuje zależności per kolumna, a jedynie zwraca informację “obiekt wykorzystuje obiekt”,
  • do wyświetlania zależności z dokładnością do kolumny w SQL Server 2008 / 2008 R2 wykorzystujemy dwie dynamiczne funkcje: sys.dm_sql_referenced_entities (obiekty wykorzystywane przez wskazany obiekt) i sys.dm_sql_referencing_entities (obiekty odwołujące się do wskazanego obiektu).

Podsumowanie

  • Na SQL Server 2000 zależności między obiektami są gubione “przy byle okazji”.
  • Na SQL Server 2005 ALTER na widoku spowodował “zgubienie” zależności między owym widokiem, a procedurą, która z widoku korzystała. Wygląda na to, że problematyczne jest śledzenie zależności na poziomie szczegółowości sięgającym kolumn (w sys.sql_dependencies dla każdej kolumny, od której zależny jest obiekt, trzymany jest jeden wiersz).
  • SQL Server 2008 (i 2008 R2) potrafi poradzić sobie z trzymaniem zależności między obiektami nawet, gdy obiekty używane przez inne obiekty zostaną usunięte i odtworzone na nowo pod nowym OBJECT_ID. Problem został rozwiązany w sposób bardzo prosty – widok sys.sql_expression_dependencies przechowuje OBJECT_ID tylko dla obiektu odwołującego się do innego obiektu, a dla obiektu “używanego” trzymana jest pełna nazwa (czteroczłonowa! z dokładnością nawet do nazwy serwera, jeżeli zależność jest między obiektami z dwóch instancji SQL Servera).
  • Do pełni szczęścia brakuje w zasadzie tylko przechowywania metadanych uwzględniających zależności, w skład których wchodzą obiekty systemowe. Cóż, może w SQL Server 2011 Microsoft wymyśli koło na nowo (w SQL Server 2000 namiastkę takiej funkcjonalności mieliśmy)… :-)

Opublikowany w MS SQL Server | Otagowane: , , , | Zostaw Komentarz »

 
Follow

Otrzymuj każdy nowy wpis na swoją skrzynkę e-mail.