USE master;
GO

IF EXISTS(SELECT *
          FROM   sys.all_objects
          WHERE  name = 'sp__COLUMN_IS_USED' 
            AND  schema_id = SCHEMA_ID('dbo'))
   EXEC ('ROP PROCEDURE dbo.sp__COLUMN_IS_USED;');
GO

--===========================================================================--
-- METADONNÉES D'UTILISATION D'UNE COLONNE                                   --
--===========================================================================--
-- Fredéric Brouard alias SQLpro                http://sqlpro.developpez.com --
-- Société SQL SPOT - http://www.sqlspot.com        2017-10-13 - version 1.0 --
--===========================================================================--


--▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄--
-- PHASE 1 : création de la procédure                                        --
--▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀--


USE master;
GO

CREATE PROCEDURE dbo.sp__COLUMN_IS_USED @OBJ NVARCHAR(261), 
                                        @COL NVARCHAR(128) = NULL
AS
/******************************************************************************
* METADONNÉES D'UTILISATION D'UNE COLONNE                                     *
* Procédure listant sous forme de tables dans quels objets est utilisé une    *
* colonne d'une table                                                         *
*******************************************************************************   
* Frédéric Brouard - SQLpro@SQLspot.com - Sté SQL SPOT http://www.sqlspot.com *
* Plus d'info. sur http://sqlpro.developpez.com   -  2017-10-13 - version 1.0 *   
*******************************************************************************   
* Cette procédure prend en argument le nom d'une table avec son schéma SQL    *
* (sinon le schéma par défaut de l'utilisateur qui la lance) ainsi qu'un nom  *
* de colonne et renvoie un jeu de données contenant le descriptif des cas     *
* d'utilisation de cette colonne dans les index et les contraintes            *
*******************************************************************************
* ATTENTION  : procédure système ! Exécutable depuis n'importe quelle base    *
*                                                                             *
* Paramètre en entrée :                                                       *
*    @OBJ type NVARCHAR(261) : nom de table dont on veut connaître les index  *
*    @COL nom de la colonne dont on veut connaître l'usage                    *
*                                                                             *
* COLONNE de la table en sortie :                                             *
*    COLUMN_NAME   nom de la colonne passée en paramètre                      *
*    OBJECT_TYPE   type d'objet (nature de la contrainte ou INDEX)            *
*    OBJECT_NAME   nom de la contrainte ou de l'index                         *

*                                                                             *
* EXEMPLE :                                                                   *
*    EXEC dbo.sp__COLUMN_IS_USED 'CC', 'Idx'                                  *
*    ... donne la liste de tous les index et contrainte de la table CC        *
*        incluant la colonne Idx                                              * 
*                                                                             *
******************************************************************************/ 

SET NOCOUNT ON;
WITH T AS (
SELECT COLUMN_NAME, CONSTRAINT_TYPE AS OBJECT_TYPE, KCU.CONSTRAINT_NAME AS OBJECT_NAME 
FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
       JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
            ON KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA  
               AND KCU.TABLE_NAME = TC.TABLE_NAME
               AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE  OBJECT_ID(KCU.TABLE_SCHEMA + '.' + KCU.TABLE_NAME) = OBJECT_ID(@OBJ) 
  AND  REPLACE(REPLACE(RTRIM(LTRIM(@COL)), ']', ''), '[', '') = COLUMN_NAME
UNION ALL
SELECT REPLACE(REPLACE(RTRIM(LTRIM(@COL)), ']', ''), '[', ''), CONSTRAINT_TYPE AS OBJECT_TYPE, CK.CONSTRAINT_NAME AS OBJECT_NAME 
FROM   INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS CK
       JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
            ON CK.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA  
               AND CK.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE  OBJECT_ID(TC.TABLE_SCHEMA + '.' + TC.TABLE_NAME) = OBJECT_ID(@OBJ) 
  AND  CHECK_CLAUSE LIKE '%' + REPLACE(REPLACE(RTRIM(LTRIM(@COL)), ']', ''), '[', '') + '%'
UNION ALL
SELECT c.name, 'INDEX', i.name 
FROM   sys.indexes AS i 
       JOIN sys.index_columns AS ic 
            ON i.object_id = ic.object_id AND i.index_id = ic.index_id 
       JOIN sys.columns AS c 
            ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE  i.object_id = OBJECT_ID(@OBJ) 
  AND  REPLACE(REPLACE(RTRIM(LTRIM(@COL)), ']', ''), '[', '') = c.name)
SELECT *
FROM   T
ORDER BY CASE OBJECT_TYPE WHEN 'PRIMARY KEY' THEN 1
                          WHEN 'UNIQUE' THEN 2
                          WHEN 'FOREIGN KEY' THEN 3
                          WHEN 'CHECK' THEN 4
                          WHEN 'INDEX' THEN 5
                          ELSE 99 END, OBJECT_NAME;                
GO


--▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄--
-- PHASE 2 : traduction en procédure système                                 --
--▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀--

EXEC sp_MS_marksystemobject 'sp__COLUMN_IS_USED';