I found something, but that means using CURSOR
DECLARE @ColumnName VARCHAR(200)
DECLARE @ColumnCount INT
DECLARE @sql VARCHAR(400)
CREATE TABLE #tempTable (Id INT)
DECLARE GetNonNullRows CURSOR
FOR
SELECT c.NAME, (SELECT COUNT(*) FROM sys.columns col WHERE col.object_id = c.OBJECT_ID) FROM sys.tables AS t
JOIN sys.columns AS c ON t.object_id = c.object_id
WHERE t.name = 'SomeTable' AND t.type = 'U'
OPEN GetNonNullRows
FETCH NEXT FROM GetNonNullRows INTO @ColumnName, @ColumnCount
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'SELECT st.UniqueId FROM SomeTable AS st WHERE ' + CONVERT(varchar, @ColumnName) + ' IS NOT NULL'
INSERT INTO #tempTable
EXEC (@sql)
FETCH NEXT FROM GetNonNullRows INTO @ColumnName, @ColumnCount
END
CLOSE GetNonNullRows
DEALLOCATE GetNonNullRows
SELECT * FROM SomeTable AS st1
WHERE st1.UniqueId IN (SELECT Id FROM #tempTable AS tt
GROUP BY Id
HAVING COUNT(Id) = @ColumnCount)
DROP TABLE #tempTable
Let me to explain this a little.
First i create cursor which iterate through all the columns of one table. For each column, I've create sql script to search in table for not null values for selected column. For those rows that satisfies criteria, I take its unique ID and put in temp table, and this job I am using for all columns.
At the end only ID's which count is like columns count are your result set, because only rows that have identical number of appearances like number of columns in table may be rows with all non null values in all columns.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…