I have a problem in SQL Sever 2008 R2... I Have a Pivot Table and rows is Dynamic created for example when execute in
Line 1
ID , Modify_Date , Line_Code , T1 , T2 , T6 , T9 , T22 , T11
Line 2 ID , Modify_Date , Line_Code , T4 , T2 , T5 , T3
and so have a different Results when execution and some fields in columns IS NULL and no have data for all rows
I want to delete Column "T9"
Thanks
You can refer this SQL: Select columns with NULL values only and rewrite the @cmd as below：
SET NOCOUNT ON;
DECLARE
@ColumnName sysname
,@DataType nvarchar(128)
,@cmd nvarchar(max)
,@TableSchema nvarchar(128) = 'dbo'
,@TableName sysname = 'TableName';--Your Table Name
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) '
+ N' ALTER TABLE ' + @TableName + N' DROP COLUMN ' + @ColumnName + N';';
EXECUTE (@cmd);
FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;
END;
CLOSE getinfo;
DEALLOCATE getinfo;
Try this optimized code
SET NOCOUNT ON;
DECLARE @ColumnName SYSNAME,
@DataType NVARCHAR(128),
@cmd NVARCHAR(max),
@TableSchema NVARCHAR(128) = 'dbo',
@TableName SYSNAME = 'TEST'; --Your Table Name
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 C.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) '
+ N' ALTER TABLE ' + @TableName
+ N' DROP COLUMN ' + @ColumnName + N';';
EXECUTE (@cmd);
FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;
END;
CLOSE getinfo;
DEALLOCATE getinfo;
