Métadonnées de l’utilisation d’une colonne

Certaines modifications de type de données d’une colonne nécessitent une suppression préalable des contraintes et index. Pour vous y aider, la procédure suivante indique dans quels objets (index ou contraintes) une colonne d’une table passée en argument est enrôlée.

Dans le cas ou vous voudriez changer le type d’une telle colonne, il faudrait agir manuellement avec le scénario suivant :

1) Scripter la création des contraintes par clic droit sur le nom de chacune des contraintes et les supprimer
2) Scripter la création des index par clic droit sur le nom de chacun des index et les supprimer
3) Modifier le type de données de la colonne avec la commande ALTER TABLE … ALTER COLUMN …
4) Recréer les index
5) Recréer les contraintes

Cette procédure à été créée pour vous y aider. Elle indique pour un couple d’information table/colonne dans quels index ou contrainte cette dernière est enrôlée…
Cette procédure est créée en tant que procédure système afin d’être exploitable dans n’importe quelle base de l’instance SQL Server.

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';

Pour utiliser cette procédure, lancez la avec EXEC et passez le nom de table éventuellement préfixé par son schéma SQL ainsi que le nom de la colonne. Exemple :

EXEC sp__COLUMN_IS_USED 'matable', 'macolonne';

LE CODE ! LE CODE ! LE CODE ! LE CODE ! LE CODE ! LE CODE ! LE CODE ! LE CODE ! LE CODE ! LE CODE !

Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES
Expert  S.G.B.D  relationnelles   et   langage  S.Q.L
Moste  Valuable  Professionnal  Microsoft  SQL Server
Société SQLspot  :  modélisation, conseil, formation,
optimisation,  audit,  tuning,  administration  SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.

L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR

MVP Microsoft SQL
Server

Développez et administrez pour la performance avec SQL Server 2014

Développez et administrez pour la performance avec SQL Server 2014

Laisser un commentaire