August 24, 2020 at 11:26 am
I need a query that will basically generate a select statement that will select all records for only the columns that have data in them .
August 24, 2020 at 6:15 pm
This is somewhat cringeworthy, but since nobody else answered...
use tempdb;
go
-- create a table to store the results:
CREATE TABLE GotData(
RecNo INT IDENTITY,
col1 char(1),
col2 char(1),
col3 char(1),
col4 char(1),
col5 char(1),
col6 char(1)
);
GO
-- populate table I'm testing... some columns contain only null values.
INSERT INTO GotData (col1,col2,col3,col4,col5,col6)
VALUES (null,'a',null,null,null,null),
('b','a',null,null,null,'c'),
('a','x',null,null,'y','d');
declare @colName NVARCHAR(10);
declare @recordCount INT;
declare @SqlStmt NVARCHAR(1000);
-- loop over the columns in the table...
declare colsCursor CURSOR FOR
SELECT ac.[name] as columnName
from sys.all_columns ac
INNER join sys.all_objects ao
ON ac.object_id = ao.object_id
WHERE ao.name = 'GotData';
OPEN colsCursor
FETCH NEXT FROM colsCursor INTO @colName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStmt = 'SELECT * FROM GotData WHERE [' + @colName + '] IS NOT NULL';
PRINT @SqlStmt;
EXECUTE sp_executesql @SqlStmt;
SET @recordCount = @@ROWCOUNT;
INSERT INTO Testdb.dbo.RecordsCheck(ColName, RecordCount) VALUES (@colName,@recordCount);
FETCH NEXT FROM colsCursor INTO @colName
END
FETCH NEXT FROM colsCursor INTO @colName
CLOSE colsCursor;
DEALLOCATE colsCursor;
August 24, 2020 at 6:34 pm
I'm not sure specifically what the OP meant by "records [presumably meaning rows] for only the columns that have data in them".
Does that mean rows with NO null columns at all? Or to SELECT only those columns that are not null in any row? Or something else??
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 25, 2020 at 6:03 am
I kinda hope there's a better way of doing this... but this works to create a SQL statement containing only the columns with data (in any of the rows)...
--DROP TABLE #RecordsCheck;
--GO
--CREATE TABLE #RecordsCheck(ColName NVARCHAR(100), RecordCount INT);
SET NOCOUNT ON;
TRUNCATE TABLE #RecordsCheck; -- empty the table so I don't get duplicates.
declare @colName NVARCHAR(10);
declare @tableName NVARCHAR(50) = 'GotData';
declare @recordCount INT;
declare @SqlStmt NVARCHAR(MAX);
declare @columnList NVARCHAR(MAX);
declare @finalSQL NVARCHAR(MAX);
-- loop over the columns in the table...
declare colsCursor CURSOR FAST_FORWARD FOR
SELECT ac.[name] as columnName
from sys.all_columns ac
INNER join sys.all_objects ao
ON ac.object_id = ao.object_id
WHERE ao.name = @tableName;
OPEN colsCursor
FETCH NEXT FROM colsCursor INTO @colName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStmt = 'SELECT 1 FROM [' + @tableName + '] WHERE [' + @colName + '] IS NOT NULL';
--PRINT @SqlStmt;
EXECUTE sp_executesql @SqlStmt; -- how do I get this result to not show anywhere?
SET @recordCount = @@ROWCOUNT;
IF @recordCount > 0
BEGIN
INSERT INTO #RecordsCheck(ColName, RecordCount) VALUES (@colName,@recordCount);
END
FETCH NEXT FROM colsCursor INTO @colName
END
FETCH NEXT FROM colsCursor INTO @colName
CLOSE colsCursor;
DEALLOCATE colsCursor;
SELECT @finalSQL = STRING_AGG(colName,', ') FROM dbo.RecordsCheck;
SET @finalSQL = 'SELECT ' + @FinalSQL + ' FROM [' + @tableName + '];'
PRINT @finalSQL;
(I wonder if this is the best way of doing it?)
Pieter
August 25, 2020 at 9:22 am
Perhaps my script Count non-NULL rows and get definition answers this? Difficult to really know what the OP is really after with such a vague requirement.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 25, 2020 at 11:26 am
I need a query that will basically generate a select statement that will select all records for only the columns that have data in them .
Does it means column doesn't have data across the rows of table?
Or
Column has data in some rows?
August 25, 2020 at 11:31 am
I'm not sure specifically what the OP meant by "records [presumably meaning rows] for only the columns that have data in them".
Does that mean rows with NO null columns at all? Or to SELECT only those columns that are not null in any row? Or something else??
Valid point Scott.
August 25, 2020 at 1:20 pm
I hate to venture a guess, but while I'm just as curious as to what the original poster meant with the extraordinarily vague wording provided, I just can't help wondering if they might have meant just not selecting any column for which all rows have a NULL value? I can recall a number of times when that would have been rather valuable as a somewhat helpful search tool when browsing data, looking for potentially useful tables, in an unfamiliar database.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 25, 2020 at 2:30 pm
That's what I assumed it meant. Other interpretations didn't make sense. I suppose writing the table name and column name to at least a temporary table and then using that to create scripts to delete unused columns makes sense.
August 25, 2020 at 2:46 pm
That's what I assumed it meant. Other interpretations didn't make sense. I suppose writing the table name and column name to at least a temporary table and then using that to create scripts to delete unused columns makes sense.
When I stop and think about how one could determine that a given column is composed entirely of NULL values, I realized quickly that for any table of any significant size, that would pretty much require a separate index for each column, just to have any shot at performing worth a darn, as you'd have to check for any non-NULL values in each column separately. If you start thinking about a rather wide table, with perhaps 70 or 80 columns or more, then this quickly gets out of hand...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 25, 2020 at 2:56 pm
(Maybe it made sense to me because I used to work on "databases" where nobody understood normalization... except my databases were small (in terms of records)... there were stupid wide tables that I had to normalize so that I could write queries...
So maybe I'm just not used to 1+ TB tables and fighting with indexes etc.
August 25, 2020 at 3:08 pm
pietlinden wrote:That's what I assumed it meant. Other interpretations didn't make sense. I suppose writing the table name and column name to at least a temporary table and then using that to create scripts to delete unused columns makes sense.
When I stop and think about how one could determine that a given column is composed entirely of NULL values, I realized quickly that for any table of any significant size, that would pretty much require a separate index for each column, just to have any shot at performing worth a darn, as you'd have to check for any non-NULL values in each column separately. If you start thinking about a rather wide table, with perhaps 70 or 80 columns or more, then this quickly gets out of hand...
Or you could do one full scan of the table and calc every column's NULL totals all in one pass. I'd probably do that instead once the table had, say, 30/40 columns or more.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 25, 2020 at 3:11 pm
If the NULL determination was not a 1-time thing, but needed to be periodically available, I'd use triggers to maintain a counter per column in another table that "told" me how many non-NULL values were present for each column in the table. When that value was 0, then I'd know that column contained only NULL values. Well-written triggers wouldn't be that much overhead.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply