Recent Posts

Archives

Categories

Meta

Függőségek megtartása (SQL 2008)

Sokszor előfordul, hogy az adatbázis módosítás elrontja a függő objektumaink működését.

Nézzünk erre egy példát:

CREATE TABLE [dbo].[testtbl](
  [a] [int] NOT NULL,
  [b] [int] NOT NULL,
  [c] [int] NOT NULL
) ON [PRIMARY]
GO
insert into v_testtbl(a,b,c) values (1,2,3)
GO
CREATE VIEW dbo.v_testtbl
AS
SELECT * FROM [testtbl]
GO
ALTER TABLE testtbl add d varchar(10)
GO
select * from v_testtbl

Sajnos a view nem fogja a d oszlopot visszaadni, de legalább az a,b,c oszlopot visszaadja. Azonban, ha újra kreálom a táblát a következő módon…:

drop table [testtbl]
GO
CREATE TABLE [dbo].[testtbl](
  [a_0] [int]  NULL,
  [a] [int] NOT NULL,
  [b] [int] NOT NULL,
  [c] [int] NOT NULL
) ON [PRIMARY]
GO
insert into testtbl(a,b,c) values (1,2,3)
GO
select * from v_testtbl

… akkor fura dolgokat ad vissza a query: a = NULL, b = 1, c = 2. Vagyis az egész elcsúszik.

Sajnos a view hibáját semmi sem fogja jelezni (az Oracle-nek van rá view-ja: user_object status=’INVALID’).

Az alábbi query az előbbi problémát jelzi:

SELECT
     ed.referencing_id,
   ed.referenced_database_name,
   COALESCE(ed.referenced_database_name + '.', '') 
    + COALESCE(ed.referenced_schema_name + '.', '')
    + ed.referenced_entity_name,
     ed.referenced_id,
   sr.name
FROM
   sys.sql_expression_dependencies ed
   LEFT OUTER JOIN sys.sysdepends sd ON ed.referencing_id = sd.id AND ed.referenced_id = sd.depid
   LEFT OUTER JOIN sysobjects sr ON ed.referencing_id = sr.id
WHERE
 sd.id IS NULL AND ed.referenced_server_name IS NULL;

Itt a name nevű oszlopba kerül az újrafordítandó objektum neve. A fordítás:

EXEC sys.sp_refreshsqlmodule N'[dbo].[v_testtbl]'

Ha a táblát csak kiegészítjük, akkor sajnos a query nem ad vissza sort, úgyhogy tábla módosításkor érdemes az összes függőséget lefordítani.

Mindenesetre van egy automatikusan lefutó metódus, de ehhez DDL triggert kell használnunk. Először is a fenti query alapján csinálnunk kell egy tároltat, ami lefordítja a kapott objektumokat:

CREATE PROCEDURE dbo.RefreshSysDepends
AS
BEGIN
      SET NOCOUNT ON;

      DECLARE
            @referencing_id         INT,
            @ref_db_name            NVARCHAR(255),
            @object_name            NVARCHAR(771),
            @referenced_id          INT,
            @sql                    NVARCHAR(600);
      DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
            FOR
            SELECT
                  ed.referencing_id,
                  ed.referenced_database_name,
                  COALESCE(ed.referenced_database_name + '.', '') 
                  + COALESCE(ed.referenced_schema_name + '.', '')
                  + ed.referenced_entity_name,
                  ed.referenced_id
            FROM
                  sys.sql_expression_dependencies ed
            LEFT OUTER JOIN
                  sys.sysdepends sd
            ON
                  ed.referencing_id = sd.id
                  AND ed.referenced_id = sd.depid
            WHERE
                  sd.id IS NULL
                  AND ed.referenced_server_name IS NULL;
      OPEN c;

      FETCH NEXT FROM c INTO @referencing_id, @ref_db_name, @object_name, @referenced_id;

      WHILE (@@FETCH_STATUS = 0)
      BEGIN
            IF @referenced_id IS NULL OR @ref_db_name IS NOT NULL
            BEGIN
                  IF @referenced_id IS NULL AND @ref_db_name IS NULL AND OBJECT_ID(@object_name) IS NULL
                        SELECT OBJECT_SCHEMA_NAME(@referencing_id) + '.' + OBJECT_NAME(@referencing_id)
                                    + ' references ' + @object_name + ' (' + RTRIM(COALESCE(@referenced_id, 0)) + '),
                                          which doesn''t exist.';

                  IF @ref_db_name IS NOT NULL AND OBJECT_ID(@object_name) IS NULL
                        SELECT OBJECT_SCHEMA_NAME(@referencing_id) + '.' + OBJECT_NAME(@referencing_id)
                                    + ' might have a cross db dependency problem with ' + @object_name + '.';
            END
            ELSE
            BEGIN
                  SET @sql = 'EXEC sys.sp_refreshsqlmodule N''' 
                        + QUOTENAME(OBJECT_SCHEMA_NAME(@referencing_id)) + '.'
                        + QUOTENAME(OBJECT_NAME(@referencing_id)) + '''';           
                  EXEC sp_executesql @sql;
                  -- you can also log the calls you make here
                  -- by storing @sql in a table along with 
                  -- event time, user information etc.
                  SELECT 'Executed SQL = ' + @sql;
            END
            FETCH NEXT FROM c INTO @referencing_id, @ref_db_name, @object_name, @referenced_id;
      END
      CLOSE c;
      DEALLOCATE c;

END
GO

Ha ezt meghívjuk kézzel, akkor ez rendbe teszi a talált hibákat, de nem javulnak ki az addig létrejövő hibák. Most hozzuk létre a triggert:

CREATE TRIGGER DDL_Catcher ON DATABASE
FOR 
      CREATE_TABLE,     ALTER_TABLE,      DROP_TABLE,
      CREATE_PROCEDURE, ALTER_PROCEDURE,  DROP_PROCEDURE,
      CREATE_FUNCTION,  ALTER_FUNCTION,   DROP_FUNCTION,
      CREATE_TRIGGER,   ALTER_TRIGGER,    DROP_TRIGGER,
      CREATE_VIEW,      ALTER_VIEW,       DROP_VIEW,
      ALTER_SCHEMA,     RENAME
      /* 
            RENAME is new in SQL Server 2008.  If you
            try this in SQL Server 2005, you will get:           
            Msg 1084, Level 15, State 1, Procedure DDL_Catcher, Line 8
            'RENAME' is an invalid event type.
      */
AS
BEGIN
      SET NOCOUNT ON;

      -- you can log this somewhere for later review, or you
      -- can just comment it out:
      SELECT N'Fired for ', EVENTDATA().value
      (
            '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
            'NVARCHAR(MAX)'
      );     
      EXEC dbo.RefreshSysDepends;
END
GO

Így már elkapja a trigger a módosításokat és meghívja a javító tárolt eljárást.

Végül egy hasznos view, ami visszaadja a lefordítandó objektumokat, az előző RefrehSysDepends tárolt helyett:

CREATE VIEW dbo.SysDependsInfo
AS
SELECT DISTINCT ResultType
     , CASE ResultType
       WHEN 'reference'  THEN referencing_qualified + ' references ' + object_name + ' (' + referenced_id + '), which doesn''t exist.'
       WHEN 'dependency' THEN referencing_qualified + ' might have a cross-db depenency problem with ' + object_name + '.'
       WHEN 'sql'        THEN 'EXEC sys.sp_refreshsqlmodule N''' + referencing_qualified + ''''
       END AS Result
FROM (
    SELECT ed.referencing_id
         , ed.referenced_database_name AS ref_db_name
         , RTRIM(COALESCE(ed.referenced_database_name + '.', '') 
           + COALESCE(ed.referenced_schema_name + '.', '')
           + ed.referenced_entity_name) AS object_name
         , RTRIM(COALESCE(ed.referenced_id, 0)) AS referenced_id
    FROM
        sys.sql_expression_dependencies ed
    LEFT OUTER JOIN
        sys.sysdepends sd
    ON
        ed.referencing_id = sd.id
        AND ed.referenced_id = sd.depid
    WHERE
        sd.id IS NULL
        AND ed.referenced_server_name IS NULL
) AS Q
OUTER APPLY (
    SELECT
        OBJECT_ID(object_name) AS object_id,
        QUOTENAME(OBJECT_SCHEMA_NAME(referencing_id)) + '.' 
        + OBJECT_NAME(referencing_id) AS referencing_qualified
    ) AS A
OUTER APPLY (
    SELECT CASE
           WHEN object_id IS NULL AND ref_db_name IS NULL     AND referenced_id IS NULL THEN 'reference'
           WHEN object_id IS NULL AND ref_db_name IS NOT NULL                           THEN 'dependency'
                                                                                        ELSE 'sql'
           END AS ResultType
    ) AS B
GO

És a hozzátartozó javító proc., kurzor nélkül:

CREATE PROCEDURE dbo.RefreshSysDepends
AS
BEGIN
SET NOCOUNT ON;

declare @sql nvarchar(max) = N''
select @sql = @sql + result + char(31) + char(10)
from dbo.SysDependsInfo
where ResultType = 'sql'
exec sp_executesql @sql;

END
GO

Ja és ha ez még nem elég akkor az alábbi linken még további query-k a függőségekről:https://www.mssqltips.com/sqlservertip/2999/different-ways-to-find-sql-server-object-dependencies/