Comment trouver les colonnes qui n'ont pas de données (toutes les valeurs sont NULL)?

J'ai plusieurs tables dans une base de données. Je voudrais trouver les colonnes (dans les tables) n'ont pas de valeurs (toutes les NULL dans une colonne). J'ai l'exemple ci-dessous, le résultat devrait être

TestTable1 --> Var2
TestTable2 --> Variable1

Je n'ai aucune idée de comment créer ce genre de requête. Votre aide est la plus appréciée!

--create first table
create table dbo.TestTable1 (
sur_id int identity(1,1) not null primary key,
var1 int null,
var2 int null
)
go

--insert some values
insert into dbo.TestTable1 (var1) 
    select 1 union all select 2 union all select 3

--create second table
create table dbo.TestTable2 (
sur_id int identity(1,1) not null primary key,
variable1 int null,
variable2 int null
)

--and insert some values
insert into dbo.TestTable2 (variable2) 
    select 1 union all select 2 union all select 3

source d'informationauteur jrara

  1. 9

    Pour une seule colonne, count(ColumnName) retourne le nombre de lignes où ColumName n'est pas nulle:

    select  count(TheColumn)
from    YourTable

    Vous pouvez générer une requête pour toutes les colonnes. Par Martin suggestion, vous pouvez exclure des colonnes qui ne peut pas être null avec is_nullable = 1. Par exemple:

    select  'count(' + name + ') as ' + name + ', '
from    sys.columns
where   object_id = object_id('YourTable')
        and is_nullable = 1

    Si le nombre de tables est grande, vous pouvez générer une requête pour toutes les tables dans une semblable manière. La liste de toutes les tables est en sys.tables.

  2. 4

    Voici un script que j'ai écrit pour faire la même chose, c'est une étape de deux processus manuel:

    1. Exécuter ce script dans SSMS et sélectionnez toutes les lignes dans le volet de Résultats:
    SÉLECTIONNEZ 
'SÉLECTIONNEZ 
COUNT( DISTINCT [' + COLUMN_NAME + ']) QUE des valeurs distinctes, 
"' + TABLE_NAME + '.' + COLUMN_NAME + "' ColumnName 
À PARTIR de 
[' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] 

L'UNION DE TOUS 
' 
À PARTIR de 
INFORMATION_SCHEMA.Les COLONNES 
COMMANDE PAR 
TABLE_NAME, 
COLUMN_NAME
    1. Coller les résultats dans une nouvelle fenêtre de requête. Faites défiler vers le bas et de supprimer la fin UNION ALL déclaration. Il ressemblera à ceci:
    SELECT COUNT( DISTINCT [ModifiedByUserId]) QUE des valeurs distinctes, à des Inspections.ModifiedByUserId " COMME ColumnName DE [dbo].[Inspections] l'UNION de TOUS 
SELECT COUNT( DISTINCT [Notes]) QUE des valeurs distinctes, à des Inspections.Les Notes " COMME ColumnName DE [dbo].[Inspections] l'UNION de TOUS 
SELECT COUNT( DISTINCT [PublicPassword]) QUE des valeurs distinctes, à des Inspections.PublicPassword " COMME ColumnName DE [dbo].[Inspections] l'UNION de TOUS 
SELECT COUNT( DISTINCT [ShopId]) QUE des valeurs distinctes, à des Inspections.ShopId " COMME ColumnName DE [dbo].[Inspections] l'UNION de TOUS 
SELECT COUNT( DISTINCT [Statut]) QUE des valeurs distinctes, à des Inspections.Le statut " en tant QUE ColumnName DE [dbo].[Inspections] l'UNION de TOUS 
SELECT COUNT( DISTINCT [SupervisorUserId]) QUE des valeurs distinctes, à des Inspections.SupervisorUserId " COMME ColumnName DE [dbo].[Inspections] l'UNION de TOUS
    1. Exécuter la requête. Il a fallu environ 6 minutes pour s'exécuter sur un 300-colonne de base de données. Il sera plus rapide ou plus lent, selon la façon dont de nombreux indices sont utilisés.
  3. 2

    C'est trop utile de ne pas être mis en pratique un peu le système de proc - surtout si vous venez d'hériter d'une base de données existante, et que vous vous demandez quelles colonnes vous pouvez les déposer ou de les ignorer.

    /*
Show the count of not-null values in a table
*/
create proc sp_aaShowAllNullColumns @tableName varchar(255) as
begin
set nocount on

declare @sql nvarchar(4000)
declare @cols nvarchar(4000)
declare @tcols table( colbit nvarchar(255) )

insert @tcols
select 'count(' + name + ') as ' + name + ', ' as colbit
from    sys.columns
where   object_id = object_id(@tableName)
and is_nullable = 1 

select @cols = coalesce( @cols, ', ', '' ) + colbit from @tcols
select @cols = substring( @cols, 1, (len(@cols) - 1) )
select @cols = isnull( @cols, '' )

select @sql = 'select count(*) as Rows' + @cols + ' from ' + @tableName
exec sp_executeSql @sql

end
go

exec sys.sp_MS_marksystemobject 'sp_aaShowAllNullColumns'
go

use Bookshop
go
exec sp_aaShowAllNullColumns 'Books'
go