170 messaggi dal 18 gennaio 2008
Ciao a tutti,
mi trovo nella situazione di dover fare una procedure che cambia tutti i varchar di un database in nvarchar. Ci sono molti campi di questi però che ho fanno parte di un indice o sono chiave e quindi alter column mi va in errore.
Qualcuno mi sa conswigliare uno script che mi dica in che indice sta il campo o se è chiave e come fare per togliere e rimettere il costrasints.

Grazie
Tommaso
salve Tommaso,
ho guardato un po' in giro perche' pensavo proprio che qualche cosa di simile fosse gia' stato scritto e pubblicato, ma non ho ancora trovato nulla di veramente interessante in questo senso, ed allora ho scritto quanto di seguito:
SET NOCOUNT ON;
USE tempdb;
GO
-- DEFINIZIONE
CREATE TABLE dbo.t0 (
  Id int NOT NULL PRIMARY KEY,
  Data varchar(9)
  );

CREATE TABLE dbo.t1_on_t0 (
  Id int NOT NULL PRIMARY KEY 
    DEFAULT 1 
    CONSTRAINT chk_id$in$ValidValues CHECK ([Id] in (1,2,3)),
  Data varchar(2) NOT NULL
    DEFAULT 'data'
    CONSTRAINT chk_data$isValid CHECK (Data LIKE '[A-Z][0-9]')
    UNIQUE
  );
CREATE TABLE dbo.t2_on_t1 (
  Id int NOT NULL PRIMARY KEY,
  IdT1 INT NOT NULL UNIQUE,
  Data varchar(9)
    DEFAULT 'pippo'
  );

ALTER TABLE dbo.t1_on_t0
  ADD CONSTRAINT fk_t1_on_t0$is$t0
    FOREIGN KEY (Id)
      REFERENCES dbo.t0(Id);

ALTER TABLE dbo.t2_on_t1
  ADD CONSTRAINT fk_t2_on_t1$is$t1
    FOREIGN KEY (IdT1)
      REFERENCES dbo.t1_on_t0(Id);


CREATE UNIQUE INDEX ix_t1_on_t0$Id_Data$U ON dbo.t1_on_t0 (Id DESC, Data ASC); 
GO
INSERT INTO dbo.t0
  VALUES (1, 'a'), (2, 'b');

INSERT INTO dbo.t1_on_t0
  VALUES (1, 'C9'), (2, 'F7');

INSERT INTO dbo.t2_on_t1
  VALUES (1, 1, 'a'), (2, 2, 'b');
GO
CREATE PROCEDURE [dbo].[usp_t1] 
AS BEGIN
  SELECT Id
    FROM dbo.t1_on_t0
END;
GO
CREATE PROCEDURE [dbo].[usp_t1_plus_t2] 
AS BEGIN
  SELECT t1.Id, t1.Data
    , t2.Id, t2.Data
    FROM dbo.t1_on_t0 t1
      LEFT JOIN dbo.t2_on_t1 t2 ON t2.IdT1 = t1.Id;
END;
GO
CREATE PROCEDURE [dbo].[usp_t0_plus_t1_plus_t2] 
AS BEGIN
  SELECT t0.Id, t0.Data 
    , t1.Id, t1.Data
    , t2.Id, t2.Data
    FROM dbo.t0 t0
      LEFT JOIN dbo.t1_on_t0 t1 ON t1.Id = t0.Id
      LEFT JOIN dbo.t2_on_t1 t2 ON t2.IdT1 = t1.Id;
END;
GO
CREATE VIEW [dbo].[v_t0_plus_t1_plus_t2] 
AS 
  SELECT t0.Id, t0.Data 
    , t1.Id AS [idT1], t1.Data AS [dataT1]
    , t2.Id AS [idT2], t2.Data AS [dataT2]
    FROM dbo.t0 t0
      LEFT JOIN dbo.t1_on_t0 t1 ON t1.Id = t0.Id
      LEFT JOIN dbo.t2_on_t1 t2 ON t2.IdT1 = t1.Id;
GO
CREATE VIEW [dbo].[v_t0_plus_t1_plus_t2$schema_bound] 
WITH SCHEMABINDING AS 
  SELECT t0.Id, t0.Data 
    , t1.Id AS [idT1], t1.Data AS [dataT1]
    , t2.Id AS [idT2], t2.Data AS [dataT2]
    FROM dbo.t0 t0
      LEFT JOIN dbo.t1_on_t0 t1 ON t1.Id = t0.Id
      LEFT JOIN dbo.t2_on_t1 t2 ON t2.IdT1 = t1.Id;
GO
CREATE FUNCTION [dbo].[udf_t0_plus_t1_plus_t2] (
  @Id int
  ) RETURNS varchar(10)
AS BEGIN
  RETURN ( SELECT t1.[Data]
        FROM dbo.t1_on_t0 t1
        WHERE t1.[Id] = @Id )
END;
GO
CREATE FUNCTION [dbo].[udf_t0_plus_t1_plus_t2$schema_bound] (
  @Id int
  ) RETURNS varchar(10) WITH SCHEMABINDING
AS BEGIN
  RETURN ( SELECT t1.[Data]
        FROM dbo.t1_on_t0 t1
        WHERE t1.[Id] = @Id )
END;
GO
CREATE TRIGGER [dbo].[tr_t1$IUD] ON [dbo].[t1_on_t0] 
  AFTER INSERT, UPDATE, DELETE
AS BEGIN
  IF (@@ROWCOUNT = 0)
    RETURN;
  SELECT i.[Data]
    FROM inserted i
      JOIN dbo.t1_on_t0 t1 ON t1.[Id] = i.[Id]
END;
GO
SET NOCOUNT ON;
DECLARE @schema SYSNAME = 'dbo';
DECLARE @table SYSNAME = 't1_on_t0';
DECLARE @column SYSNAME = 'id';

PRINT 'RICERCA DIPENDENZE DELLA COLONNA ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '.' + QUOTENAME(@column);
PRINT '';

--SELECT o.[object_id], o.[name]
--  FROM sys.objects o 
--    INNER JOIN sys.schemas s ON s.[schema_id] = o.[schema_id]
--  WHERE 
--    (o.[name] = @table AND s.name = @schema)

PRINT 'Indici e primary key basati sulla colonna ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '.' + QUOTENAME(@column);
SELECT ix.[name] AS index_name  
  , ix.[type_desc]
  , CASE WHEN ix.[is_primary_key] = 1THEN 'PK' END AS [tipo]
  FROM sys.indexes ix
    INNER JOIN sys.index_columns AS ixc ON ix.object_id = ixc.object_id AND ix.index_id = ixc.index_id  
    INNER JOIN sys.tables t ON t.[object_id] = ix.[object_id]
    INNER JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
  WHERE 
    (t.[name] = @table AND s.name = @schema  AND COL_NAME(ixc.[object_id], ixc.[column_id]) = @column)
    AND
    -- excluede STATISTICS idx + HEAP 
    (ix.[is_hypothetical] = 0 AND ix.[index_id] > 0);


PRINT 'DEFAULT constraints basati sulla colonna ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '.' + QUOTENAME(@column);
SELECT df.[name]
  , df.[definition]
  FROM sys.default_constraints df
    INNER JOIN sys.columns c  ON c.[default_object_id] = df.[object_id]
    INNER JOIN sys.tables t ON t.[object_id] = df.[parent_object_id]
    INNER JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
  WHERE 
    (t.[name] = @table AND s.name = @schema  AND c.[name] = @column);

PRINT 'CHECK constraints basati sulla colonna ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '.' + QUOTENAME(@column);
SELECT DISTINCT chk.[name]
  , chk.[definition]
  FROM sys.check_constraints chk
    INNER JOIN sys.columns c ON c.[object_id] = chk.[parent_object_id] AND c.[column_id] = chk.[parent_column_id]
    INNER JOIN sys.tables t ON t.[object_id] = chk.[parent_object_id]
    INNER JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
  WHERE 
    (t.[name] = @table AND s.name = @schema  AND c.[name] = @column);

PRINT 'FOREIGN KEY constraints basati sulla colonna ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '.' + QUOTENAME(@column);
SELECT fk.[name] 
  , CASE WHEN OBJECT_NAME(fk.[parent_object_id]) = @table AND s.name = @schema THEN      'ReferenZIANTE  ' 
      WHEN OBJECT_NAME(fk.[referenced_object_id]) = @table AND s.name = @schema THEN 'ReferenZIATA DA' 
      ELSE '*** err ***' END 
    + ': -> ' +
    CASE WHEN OBJECT_NAME(fk.[parent_object_id]) = @table AND s.name = @schema THEN OBJECT_NAME(fk.[referenced_object_id])
      WHEN OBJECT_NAME(fk.[referenced_object_id]) = @table AND s.name = @schema THEN OBJECT_NAME(fk.[parent_object_id])
      ELSE '*** err ***' END AS [Indicazioni]
  , COL_NAME(fkc.parent_object_id,fkc.parent_column_id) [Riferimento Colonna Esterna]
  --, *
  FROM sys.foreign_keys fk
    INNER JOIN sys.tables t ON t.[object_id] = fk.[parent_object_id]
    INNER JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
    INNER JOIN sys.foreign_key_columns fkc ON fkc.[constraint_object_id] = fk.[object_id]
  WHERE 
    (OBJECT_NAME(fk.[parent_object_id]) = @table OR OBJECT_NAME(fk.[referenced_object_id]) = @table)
    AND s.name = @schema
    AND (COL_NAME(fkc.[parent_object_id], fkc.[parent_column_id]) = @column OR COL_NAME(fkc.[referenced_object_id], fkc.[referenced_column_id]) = @column)
  ORDER BY CASE WHEN OBJECT_NAME(fk.[parent_object_id]) = @table AND s.name = @schema THEN 0 ELSE 1 END;


PRINT 'ALTRi riferimenti alla colonna ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '.' + QUOTENAME(@column);
PRINT '   warning: se una oggetto tipo vista/procedura/... esprime una "SELECT * " che ovviamente coinvolge anche la colonna implicata,';
PRINT '            il riferimento alla colonna risulta IMPLICITO ma non riconoscibile...';

SELECT DISTINCT o.[type_desc] 
  , OBJECT_NAME (d.[referencing_id]) AS [Oggetto Referente]
  , d.[is_schema_bound_reference] [schema_bound]
  FROM sys.sql_expression_dependencies d
    INNER JOIN sys.objects o ON o.[object_id] = d.[referencing_id]
  WHERE 
    d.[referenced_entity_name] = @table
    AND OBJECT_SCHEMA_NAME(o.[object_id]) = @schema
    AND OBJECT_DEFINITION (referencing_id) LIKE '%'+ @column + '%' --'%ColumnName%'
    -- esclude CHECK, DEFAULT, FOREIGN KEY, PRIMARY KEY, UNIQUE IX
    AND o.[type] NOT IN ('C', 'D', 'F', 'PK', 'UQ')
  ORDER BY OBJECT_NAME(referencing_id);  

GO
-- CLEAN UP
DROP FUNCTION [dbo].[udf_t0_plus_t1_plus_t2$schema_bound], [dbo].[udf_t0_plus_t1_plus_t2]
DROP VIEW [dbo].[v_t0_plus_t1_plus_t2], [dbo].[v_t0_plus_t1_plus_t2$schema_bound];
DROP PROCEDURE [dbo].[usp_t1_plus_t2], [dbo].[usp_t1], [dbo].[usp_t0_plus_t1_plus_t2] ;
DROP TABLE dbo.t2_on_t1, dbo.t1_on_t0, dbo.t0;
--<----------
RICERCA DIPENDENZE DELLA COLONNA [dbo].[t1_on_t0].[id]
 
Indici e primary key basati sulla colonna [dbo].[t1_on_t0].[id]
index_name                          type_desc       tipo
----------------------------------- --------------- ----
PK__t1_on_t0__3214EC0748A88B66      CLUSTERED       PK
ix_t1_on_t0$Id_Data$U               NONCLUSTERED    NULL

DEFAULT constraints basati sulla colonna [dbo].[t1_on_t0].[id]
name                                definition
----------------------------------- ----------------
DF__t1_on_t0__Id__0D44F85C          ((1))

CHECK constraints basati sulla colonna [dbo].[t1_on_t0].[id]
name                                definition
----------------------------------- --------------------------------------
chk_id$in$ValidValues               ([Id]=(3) OR [Id]=(2) OR [Id]=(1))

FOREIGN KEY constraints basati sulla colonna [dbo].[t1_on_t0].[id]
name                                Indicazioni                       Riferimento Colonna Esterna
----------------------------------- --------------------------------- -----------------------------
fk_t1_on_t0$is$t0                   ReferenZIANTE  : -> t0            Id
fk_t2_on_t1$is$t1                   ReferenZIATA DA: -> t2_on_t1      IdT1

ALTRi riferimenti alla colonna [dbo].[t1_on_t0].[id]
   warning: se una oggetto tipo vista/procedura/... esprime una "SELECT * " che ovviamente coinvolge anche la colonna implicata,
            il riferimento alla colonna risulta IMPLICITO ma non riconoscibile...
type_desc               Oggetto Referente                         schema_bound
----------------------- ----------------------------------------- ------------------------------------
SQL_TRIGGER             tr_t1$IUD                                 0
SQL_SCALAR_FUNCTION     udf_t0_plus_t1_plus_t2                    0
SQL_SCALAR_FUNCTION     udf_t0_plus_t1_plus_t2$schema_bound       1
SQL_STORED_PROCEDURE    usp_t0_plus_t1_plus_t2                    0
SQL_STORED_PROCEDURE    usp_t1                                    0
SQL_STORED_PROCEDURE    usp_t1_plus_t2                            0
VIEW                    v_t0_plus_t1_plus_t2                      0
VIEW                    v_t0_plus_t1_plus_t2$schema_bound         1



che sono 4 righe di codice basate su CATALOG DMVs al fine di ricercare le dipendenze relative ad una specifica colonna, cosi' da poter ispezionare gli oggetti referenzianti, effettuarne il drop preventivo alle operazione di cambiamento a te interessanti per poi rigenerarli dove/come si conviene.
L'ultima query, come evidente, ricerca riferimenti alla colonna specifica presenti nella dichiarazione degli oggetti; qualora il codice preveda ad esempio una proiezione "SELECT *" non sara' possibile a livello procedurale ottenere un riferimento alla colonna comunque coinvolta in quanto il Query Optimizer "realizzera'" il riferimento nella seconda fase operativa; la prima fase consiste nel ricevere il comando SQL da eseguire ed effettuarne il parsing, al fine di produrre il "Parse Tree"; la seconda fase esegue il binding a partire da questo Parse Tree al fine di emettere l'"Algebrized Tree", che verra' poi processato nella fase di Query Optimization per produrre l'Execution Plan da eseguire; questo "tutto molto brevemente" :) ... se lo desideri possiamo argomentare meglio, che queste cose mi piacciono molto :)

Questa premessa, ovviamente riguarda "bad code", codice "scritto male", perche' non devo sicuramente dire a nessuno che, mai e poi mai, "e' intelligente scrivere SELECT * " :)
le conseguenze sono solitamente la perdita di tutti i capelli :D

per quanto riguarda l'indicazione [schema_bound] dell'ultima query, questo attributo indica che gli oggetti relativi ovviamente sono stati dichiarati WITH SCHEMABINDING; cio' NON indica che nel caso di riscontro negativo sia possibile alterare la base table referenziata senza prima distruggere gli oggetti referenzianti, in quanto questa specifica vale unicamente per le STORED PROCEDURES...
infatti, solamente per le STORED PROCEDURES, vale la regola generale della Deferred Resolution, cioe' un "oggetto" referenziato nel corpo di una procedura puo' anche NON esistere al momento della creazione della stessa, in quanto SQL Server verifica solamente la validita' sintattica dei comandi SQL presenti nel codice, mentre la validazione funzionale sara' effettuata solo al momento della prima compilazione della procedura, quindi verra' sollevato un'eccezione in caso di incongruenze. I BOL indicano che
"Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.
"
ma nel caso di rimozione di una colonna successivamente alla generazione della procedura, come puo' succedere nel tuo caso, non verranno sollevate eccezioni sino a quando la procedura non necessitera' di essere nuovamente compilata.

Per quanto riguarda invece le foreign keys, ovviamente devi anche verificare al congruita' del nuovo tipo di dato associato all'attributo che andrai a modificate, che dovra' essere compatibile con la colonna relativa dell'altra entita' coinvolta nel constraint di foreign key.

un salutone
Modificato da Andrea Montanari il 20 ottobre 2017 13.05 -

Andrea Montanari
http://www.hotelsole.com - http://www.hotelsole.com/asql/index.php

Torna al forum | Feed RSS

ASPItalia.com non è responsabile per il contenuto dei messaggi presenti su questo servizio, non avendo nessun controllo sui messaggi postati nei propri forum, che rappresentano l'espressione del pensiero degli autori.