Une procédure pour savoir si une table contient un LOB

Cette procédure renvoie 0 ou 1 si la table contient un LOB, par exemple TEXT, NTEXT, IMAGE, VARCHAR(max), NVARCHAR(max), VARBINARY(max), XML, GEOMETRY, GEOGRAPHY…

En fait cette procédure recherche si votre table contient une colonne de type non atomique.


CREATE PROCEDURE dbo.sp_TABLE_HAS_LOB @OID INT, @RETVAL BIT OUTPUT
AS
/*******************************************************************************
* Fred Brouard / SQLpro - 2011-09-17 - sqlpro.developpez.com - www.sqlspot.com *
********************************************************************************
* PROCEDURE dbo.sp_TABLE_HAS_LOB : teste si une table contient un type LOB ou  *
*                                  dérivé (CLR par exemple)                    *
*                                 bits (chaine composée de zéros et de uns)    *
* PARAMETRES : IN  @OID object_id de la table (int)                            *
*              OUT @RETVAL bit, 0 si pas de LOB, 1 si contient un LOB          *
********************************************************************************
* Exemple :                                                                    *
*                                                                              *
*   USE msdb                                                                   *
*   DECLARE @RETVAL BIT, @OID INT;                                             *
*   SET @OID = object_ID('dbo.sysjobsteps');                                   *
*   EXEC dbo.sp_TABLE_HAS_LOB @OID, @RETVAL OUTPUT;                            *
*   SELECT @RETVAL; => 1                                                       *
*                                                                              *
* NOTA : avec la table dbo.sysjobs la procédure retourne 0                     *
*******************************************************************************/  
BEGIN
   SET @RETVAL = NULL;
   IF NOT EXISTS(SELECT *
                 FROM   sys.columns
                 WHERE  (object_id = @OID))
      RETURN;
   SET @RETVAL =  
      CASE  
         WHEN EXISTS(SELECT *
                     FROM   sys.columns AS c
                            INNER JOIN sys.types AS t
                                  ON c.system_type_id = t.system_type_id
                     WHERE  (object_id = @OID
                       AND  t.name NOT IN  
                                      ('uniqueidentifier',  
                                       'date',
                                       'time',
                                       'datetime2',
                                       'datetimeoffset',
                                       'tinyint',
                                       'smallint',
                                       'int',
                                       'smalldatetime',
                                       'real',
                                       'money',
                                       'datetime',
                                       'float',
                                       'bit',
                                       'decimal',
                                       'numeric',
                                       'smallmoney',
                                       'bigint',
                                       'varchar', --> MAX !
                                       'binary',
                                       'varbinary',
                                       'char',
                                       'timestamp',
                                       'nvarchar', --> MAX !
                                       'nchar',
                                       'sysname'))
                               OR (object_id = @OID        
                                   AND c.max_length <= 0))
                THEN 1
                ELSE 0
           END;
END;
GO

Exemple d’utilisation :

USE msdb
 
DECLARE @RETVAL BIT, @OID INT;
SET @OID = object_ID('dbo.sysjobsteps');
EXEC dbo.sp_TABLE_HAS_LOB @OID, @RETVAL OUTPUT;
SELECT @RETVAL;

Renvoi 1

DECLARE @RETVAL BIT, @OID INT;
SET @OID = object_ID('dbo.sysjobs');
EXEC dbo.sp_TABLE_HAS_LOB @OID, @RETVAL OUTPUT;
SELECT @RETVAL;

Renvoi 0


--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *

MVP Microsoft SQL Server

Laisser un commentaire