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