Comment détecter et supprimer une colonne contenant uniquement des valeurs null?

ALTER TABLE table1 DROP COLUMN c

SQL est davantage à fonctionner sur rangées em> plutôt que les colonnes em>.

Si vous parlez de la suppression des lignes où c est null, utilisez: P>

alter table table1 drop column c

Voici un Proc stocké rapide (et laid) qui prend le nom de la table et imprimer (ou déposer si vous le souhaitez) les champs pleins de NULLS.

ALTER procedure mysp_DropEmptyColumns 
  @tableName nvarchar(max)
as begin
  declare @FieldName nvarchar(max)
  declare @SQL nvarchar(max)
  declare @CountDef nvarchar(max)
  declare @FieldCount int

  declare fieldNames cursor  local fast_forward for
    select c.name
      from syscolumns c 
        inner join sysobjects o on c.id=o.id
      where o.xtype='U'
        and o.Name=@tableName

  open fieldNames 
  fetch next from fieldNames into @FieldName
  while (@@fetch_status=0)
  begin
    set @SQL=N'select @Count=count(*) from "'+@TableName+'" where "'+@FieldName+'" is not null'
    SET @CountDef = N'@Count int output';
    exec sp_executeSQL @SQL, @CountDef, @Count = @FieldCount output
    if (@FieldCount=0)
    begin
      set @SQL = 'alter table '+@TableName+' drop column '+@FieldName
      /* exec sp_executeSQL @SQL */
      print @SQL
    end
    fetch next from fieldNames into @FieldName
  end

  close fieldNames
end

Comment détecter si une colonne donnée n'a que la valeur code> null code>:

SELECT 1  -- no GROUP BY therefore use a literal
  FROM Locations
HAVING COUNT(a) = 0 
       AND COUNT(*) > 0;

Essayez cette procédure stockée avec votre nom de table comme entrée.

alter proc USP_DropEmptyColumns 

@TableName varchar(255)
as
begin

Declare @col varchar(255), @cmd varchar(max)

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = @TableName

OPEN getinfo

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM [' + @TableName + '] WHERE [' + @col + '] IS NOT NULL) 

                        BEGIN 
                        ALTER TABLE [' + @TableName + ']  DROP Column [' + @col + ']
                        end'
    EXEC(@cmd)

    FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo

end

PROC PRINT DATA=TABLE1;RUN;

PROC TRANSPOSE DATA=TABLE1 OUT=TRANS1;VAR A B C D E;RUN;

DATA TRANS2;SET TRANS1;IF COL1 = . AND COL2 = . THEN DELETE;RUN;

PROC TRANSPOSE DATA=TRANS2 OUT=TABLE2 (DROP=_NAME_);VAR COL1-COL2;RUN;

PROC PRINT DATA=TABLE2;RUN;