Datafly.pro

Voici le moyen de localiser les NULLS dans une base de données :



Pour obtenir ce résultat, nous allons créer deux objets :

• Une fonction NbRows (@Table VARCHAR(100)) RETURNS BIGINT qui nous facilitera la vie pour connaitre le nombre de lignes d'une table,
• Une procédure NullsTest, qui fera l'inspection de toutes les tables de la base. Un paramètre @NbMaxRows (10000 par défaut) permettra de limiter la recherche aux tables dont le nombre de lignes est inférieur à cette valeur.

/*-------------------------------------------------------------------
[SCRIPT]   Nombre de lignes d'une table
[DATABASE] 
[DESCRIPTION] Relève le nombre de lignes d'une table dans le catalogue
[MAJ PAR] DATAFLY - Arian Papillon
[DATEMAJ]  2013
-------------------------------------------------------------------*/
CREATE FUNCTION NbRows(@Table VARCHAR(100))
RETURNS BIGINT
AS BEGIN
DECLARE @nb bigint
SELECT  @nb=SUM(p.rows) 
FROM    sys.tables t
        JOIN sys.indexes i ON t.object_id = i.object_id
        JOIN sys.partitions p ON i.object_id = p.object_id
                                 AND i.index_id = p.index_id
 WHERE   t.type = 'U' AND i.index_id < 2 AND t.OBJECT_ID = OBJECT_ID(@Table)
 RETURN @nb
 END

GO

/*-------------------------------------------------------------------
[SCRIPT] Procédure NullsTest  
[DATABASE] 
[DESCRIPTION] Test de NULL sur toutes les colonnes nullables, 
tables pour lesquelles rows < @NbMaxRows (10000 lignes par défaut)
[MAJ PAR] DATAFLY - Arian Papillon
[DATEMAJ]  2013
-------------------------------------------------------------------*/
CREATE PROC NullsTest
@NbMaxRows BIGINT = 10000
AS
IF OBJECT_ID('tempdb.dbo.#Nullables') IS NOT NULL DROP TABLE #Nullables
CREATE TABLE #Nullables (TableName VARCHAR(200), ColumnName VARCHAR(200),NbNulls BIGINT, Nbrows BIGINT)
DECLARE @id INT, @TableName VARCHAR(200), @ColumnName VARCHAR(200),@SQLCmd VARCHAR(4000),@Nulls INT
DECLARE TestCurs CURSOR
FOR
    SELECT  o.OBJECT_ID AS Id
        , SCHEMA_NAME(schema_id)+'.['+o.name + ']' AS TableName
        , '['+c.name+']' AS ColumnName
    FROM      sys.columns c
          JOIN sys.objects o ON c.object_id = o.object_id
    WHERE      o.type_desc = 'USER_TABLE'
          AND c.is_nullable = 1
          AND dbo.nbrows(SCHEMA_NAME(schema_id)+'.'+o.NAME) BETWEEN 1 AND @NbMaxRows
          ORDER BY SCHEMA_NAME(schema_id)+'.'+o.name, c.name

OPEN TestCurs
FETCH NEXT FROM TestCurs INTO @Id,@TableName,@ColumnName
WHILE @@fetch_status = 0 
    BEGIN
    SET @SQLCmd =  'INSERT #Nullables (TableName,ColumnName,NbNulls,NbRows) VALUES ('''+@TableName+''','''+@ColumnName+''', (SELECT COUNT(*) FROM '+@TableName+' WHERE '+@ColumnName+ ' IS NULL),dbo.Nbrows('''+@TableName+'''))'
    PRINT @SQLCmd
    EXEC(@SQLCmd)
    FETCH NEXT FROM TestCurs INTO @Id,@TableName,@ColumnName
    END 
 CLOSE TestCurs
 DEALLOCATE TestCurs
 SELECT * FROM #Nullables     

GO

Une fois ces objets créés dans la base de données à inspecter, il suffit maintenant de lancer :

EXEC NullsTest -- explore les tables contenant jusqu'à 10000 lignes
ou
EXEC NullsTest 20000  -- (le paramètre 20000 permet d'explorer les tables contenant jusqu'à 20000 lignes)

Bonne exploration de NULLS !