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