sql server - importar - SQL: seleccione columnas solo con valores NULL

ejecutar bcp desde sql server

Question

¿Cómo selecciono todas las columnas en una tabla que solo contiene valores NULOS para todas las filas? Estoy usando MS SQL Server 2005 . Estoy tratando de averiguar qué columnas no se usan en la tabla para poder eliminarlas.

Answer 1

¿O solo quería ver si una columna solo tiene valores NULL (y, por lo tanto, probablemente no se use)?

Una aclaración adicional de la pregunta podría ayudar.

EDITAR: Ok ... aquí hay un código realmente difícil para que empieces ...

SET NOCOUNT ON
DECLARE @TableName Varchar(100)
SET @TableName=''YourTableName''
CREATE TABLE #NullColumns (ColumnName Varchar(100), OnlyNulls BIT)
INSERT INTO #NullColumns (ColumnName, OnlyNulls) SELECT c.name, 0 FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id AND o.name = @TableName AND o.xtype = ''U''
DECLARE @DynamicSQL AS Nvarchar(2000)
DECLARE @ColumnName Varchar(100)
DECLARE @RC INT
SELECT TOP 1 @ColumnName = ColumnName FROM #NullColumns WHERE OnlyNulls=0
WHILE @@ROWCOUNT > 0
BEGIN
SET @RC=0
SET @DynamicSQL = ''SELECT TOP 1 1 As HasNonNulls FROM '' + @TableName + '' (nolock) WHERE '''''' + @ColumnName + '''''' IS NOT NULL''
EXEC sp_executesql @DynamicSQL
set @RC=@@rowcount
IF @RC=1
BEGIN
SET @DynamicSQL = ''UPDATE #NullColumns SET OnlyNulls=1 WHERE ColumnName='''''' + @ColumnName + ''''''''
EXEC sp_executesql @DynamicSQL
END
ELSE
BEGIN
SET @DynamicSQL = ''DELETE FROM #NullColumns WHERE ColumnName='''''' + @ColumnName+ ''''''''
EXEC sp_executesql @DynamicSQL
END
SELECT TOP 1 @ColumnName = ColumnName FROM #NullColumns WHERE OnlyNulls=0
END
SELECT * FROM #NullColumns
DROP TABLE #NullColumns
SET NOCOUNT OFF

Sí, hay formas más fáciles, pero tengo una reunión para ir ahora mismo. ¡Buena suerte!

Answer 2

Aquí está la versión sql 2005 o posterior: Reemplace ADDR_Address con su nombre de tabla.

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 = ''ADDR_Address''
OPEN getinfo
FETCH NEXT FROM getinfo into @col
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @cmd = ''IF NOT EXISTS (SELECT top 1 * FROM ADDR_Address WHERE ['' + @col + ''] IS NOT NULL) BEGIN print '''''' + @col + '''''' end''
EXEC(@cmd)
FETCH NEXT FROM getinfo into @col
END
CLOSE getinfo
DEALLOCATE getinfo

Answer 3

Aquí hay una versión actualizada de la consulta de Bryan para 2008 y posteriores. Utiliza INFORMATION_SCHEMA.COLUMNS, agrega variables para el esquema de la tabla y el nombre de la tabla. El tipo de datos de la columna se agregó a la salida. Incluir el tipo de datos de columna ayuda cuando se busca una columna de un tipo de datos en particular. No agregué el ancho de las columnas ni nada.

Para la salida, el RAISERROR ... CON NOWAIT se usa para que el texto se muestre inmediatamente en lugar de todos a la vez (en su mayor parte) al final como lo hace PRINT.

SET NOCOUNT ON;
DECLARE
@ColumnName sysname
,@DataType nvarchar(128)
,@cmd nvarchar(max)
,@TableSchema nvarchar(128) = ''dbo''
,@TableName sysname = ''TableName'';
DECLARE getinfo CURSOR FOR
SELECT
c.COLUMN_NAME
,c.DATA_TYPE
FROM
INFORMATION_SCHEMA.COLUMNS AS c
WHERE
c.TABLE_SCHEMA = @TableSchema
AND c.TABLE_NAME = @TableName;
OPEN getinfo;
FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = N''IF NOT EXISTS (SELECT * FROM '' + @TableSchema + N''.'' + @TableName + N'' WHERE ['' + @ColumnName + N''] IS NOT NULL) RAISERROR('''''' + @ColumnName + N'' ('' + @DataType + N'')'''', 0, 0) WITH NOWAIT;'';
EXECUTE (@cmd);
FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;
END;
CLOSE getinfo;
DEALLOCATE getinfo;

Answer 4

Es posible que necesite aclarar un poco. ¿Qué estás realmente tratando de lograr? Si realmente desea averiguar los nombres de las columnas que solo contienen valores nulos, entonces deberá recorrer el esquema y realizar una consulta dinámica en función de eso.

No sé qué DBMS estás usando, así que pondré un pseudo código aquí.

for each col
begin
@cmd = ''if not exists (select * from tablename where '' + col + '' is not null begin print '' + col + '' end''
exec(@cmd)
end

Answer 5

Esto debería darle una lista de todas las columnas en la tabla "Persona" que solo tiene valores NULL. Obtendrá los resultados como conjuntos de resultados múltiples, que están vacíos o contienen el nombre de una sola columna. Necesita reemplazar "Persona" en dos lugares para usarlo con otra tabla.

DECLARE crs CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM syscolumns WHERE id=OBJECT_ID(''Person'')
OPEN crs
DECLARE @name sysname
FETCH NEXT FROM crs INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC(''SELECT '''''' + @name + '''''' WHERE NOT EXISTS (SELECT * FROM Person WHERE '' + @name + '' IS NOT NULL)'')
FETCH NEXT FROM crs INTO @name
END
CLOSE crs
DEALLOCATE crs

Answer 6

No estoy seguro sobre 2005, pero 2008 lo comí:

USE [DATABASE_NAME] -- !
GO
DECLARE @SQL NVARCHAR(MAX)
DECLARE @TableName VARCHAR(255)
SET @TableName = ''TABLE_NAME'' -- !
SELECT @SQL =
(
SELECT
CHAR(10)
+''DELETE FROM [''+t1.TABLE_CATALOG+''].[''+t1.TABLE_SCHEMA+''].[''+t1.TABLE_NAME+''] WHERE ''
+(
SELECT
CASE t2.ORDINAL_POSITION
WHEN (SELECT MIN(t3.ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS t3 WHERE t3.TABLE_NAME=t2.TABLE_NAME) THEN ''''
ELSE ''AND ''
END
+''[''+COLUMN_NAME+''] IS NULL'' AS ''data()''
FROM INFORMATION_SCHEMA.COLUMNS t2 WHERE t2.TABLE_NAME=t1.TABLE_NAME FOR XML PATH('''')
) AS ''data()''
FROM INFORMATION_SCHEMA.TABLES t1 WHERE t1.TABLE_NAME = @TableName FOR XML PATH('''')
)
SELECT @SQL -- EXEC(@SQL)

Answer 7

Prueba esto -

DECLARE @table VARCHAR(100) = ''dbo.table''
DECLARE @sql NVARCHAR(MAX) = ''''
SELECT @sql = @sql + ''IF NOT EXISTS(SELECT 1 FROM '' + @table + '' WHERE '' + c.name + '' IS NOT NULL) PRINT '''''' + c.name + ''''''''
FROM sys.objects o
JOIN sys.columns c ON o.[object_id] = c.[object_id]
WHERE o.[type] = ''U''
AND o.[object_id] = OBJECT_ID(@table)
AND c.is_nullable = 1
EXEC(@sql)

Answer 8

Si necesita listar todas las filas donde todos los valores de columna son NULL , entonces usaría la función COLLATE . Esto toma una lista de valores y devuelve el primer valor no nulo. Si agrega todos los nombres de columna a la lista, entonces use IS NULL , debería obtener todas las filas que contienen solo nulos.

SELECT * FROM MyTable WHERE COLLATE(Col1, Col2, Col3, Col4......) IS NULL

En realidad, no debería tener tablas con TODAS las columns nulas, ya que esto significa que no tiene una primary key (no se permite que sea null ). No tener una clave principal es algo que debe evitarse; esto rompe la primera forma normal.

Answer 9

También recomendaría buscar campos que tengan el mismo valor, no solo NULL.

Es decir, para cada columna en cada tabla haga la consulta:

SELECT COUNT(DISTINCT field) FROM tableName

y concentrarse en aquellos que devuelven 1 como resultado.

Answer 10

Tendrás que recorrer el conjunto de columnas y verificar cada una. Debería poder obtener una lista de todas las columnas con un comando de tabla DESCRIBE.

Pseudo-código:

foreach $column ($cols) {
query("SELECT count(*) FROM table WHERE $column IS NOT NULL")
if($result is zero) {
# $column contains only null values"
push @onlyNullColumns, $column;
} else {
# $column contains non-null values
}
}
return @onlyNullColumns;

Sé que esto parece un poco contradictorio, pero SQL no proporciona un método nativo para seleccionar columnas, solo filas.

Answer 11

Tu puedes hacer:

select
count(<columnName>)
from
<tableName>

Si el recuento devuelve 0, significa que todas las filas en esa columna son NULL (o no hay filas en absoluto en la tabla)

puede ser cambiado a

select
case(count(<columnName>)) when 0 then ''Nulls Only'' else ''Some Values'' end
from
<tableName>

Si desea automatizarlo, puede usar las tablas del sistema para repetir los nombres de las columnas en la tabla que le interesa.

Answer 12

Una versión actualizada de la versión ''user2466387'', con una pequeña prueba adicional que puede mejorar el rendimiento, porque es inútil probar columnas que no admiten nulos:

AND IS_NULLABLE = ''YES''

El código completo:

SET NOCOUNT ON;
DECLARE
@ColumnName sysname
,@DataType nvarchar(128)
,@cmd nvarchar(max)
,@TableSchema nvarchar(128) = ''dbo''
,@TableName sysname = ''TableName'';
DECLARE getinfo CURSOR FOR
SELECT
c.COLUMN_NAME
,c.DATA_TYPE
FROM
INFORMATION_SCHEMA.COLUMNS AS c
WHERE
c.TABLE_SCHEMA = @TableSchema
AND c.TABLE_NAME = @TableName
AND IS_NULLABLE = ''YES'';
OPEN getinfo;
FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = N''IF NOT EXISTS (SELECT * FROM '' + @TableSchema + N''.'' + @TableName + N'' WHERE ['' + @ColumnName + N''] IS NOT NULL) RAISERROR('''''' + @ColumnName + N'' ('' + @DataType + N'')'''', 0, 0) WITH NOWAIT;'';
EXECUTE (@cmd);
FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;
END;
CLOSE getinfo;
DEALLOCATE getinfo;

Answer 13

SELECT t.column_name
FROM user_tab_columns t
WHERE t.nullable = ''Y'' AND t.table_name = ''table name here'' AND t.num_distinct = 0;

Answer 14

SELECT cols
FROM table
WHERE cols IS NULL