Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    35

    Question Unanswered: Query Help, What columns for all records are empty

    I am wanting to make a query to find out if there are any columns in a table that are null for all records.

    So far I am using a cursor that holds all the column names of that table and then a stored procedure that would use a loop to cycle through all the column names in a query to essentially count the number of nulls for each column and then an If statement to check to see if the #of nulls = to the number of records.

    What isn't working is my query to count the number of nulls...Here is my query

    select count(*)
    from XYZ
    where @cName is null

    Ive tried declaring cName as a sysname, and varchar, neither work.

    Also, I've tried using COL_NAME(OBJECT_ID('XYZ'), someColumnIndx)...I think this would be the better choice just because I wouldn't need to use a cursor, but both aren't working.
    I am a bit new to MSSQL and don't know my way around the system tables. Is there a sys table that would give me this info so I wouldn't need to perform a query?

    Any info would be great.
    "My Idea is that everyone should be required to use small fonts. That way we'll save disk space.
    ~Asok.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That was fun....


    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname, COLUMN_NAME sysname, NUM_NULLS int)
    GO
    
    DECLARE @sql varchar(8000)
    
    DECLARE myCursor99 CURSOR
    FOR
    SELECT 'INSERT INTO myTable99(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, NUM_NULLS) '
    	+ ' SELECT ' 
    	+ '''' + '[' + c.TABLE_CATALOG   + ']' + ''''+ ',' 
    	+ '''' + '[' + c.TABLE_SCHEMA + ']' + '''' + ','
    	+ '''' + '[' + c.TABLE_NAME   + ']' + '''' + ',' 
    	+ '''' + '[' + c.COLUMN_NAME  + ']' + '''' + ','
    	+ 'COUNT(*) FROM [' + c.TABLE_NAME + '] WHERE [' + c.COLUMN_NAME + '] IS NULL'
    	+ ' IF @@ERROR <> 0 '
    	+ ' SELECT ' 
    	+ '''' + '[' + c.TABLE_CATALOG   + ']' + ''''+ ',' 
    	+ '''' + '[' + c.TABLE_SCHEMA + ']' + '''' + ','
    	+ '''' + '[' + c.TABLE_NAME   + ']' + '''' + ',' 
    	+ '''' + '[' + c.COLUMN_NAME  + ']' + ''''
          FROM INFORMATION_SCHEMA.Columns c
    INNER JOIN INFORMATION_SCHEMA.Tables t
    	ON c.TABLE_CATALOG = t.TABLE_CATALOG
           AND c.TABLE_SCHEMA  = t.TABLE_SCHEMA
           AND c.TABLE_NAME    = t.TABLE_NAME
         WHERE IS_NULLABLE = 'YES' 
           AND TABLE_TYPE = 'BASE TABLE'
    
    OPEN myCursor99
    
    FETCH NEXT FROM myCursor99 INTO @sql
    
    WHILE @@FETCH_STATUS = 0
      BEGIN
    	EXEC(@SQL)
    	FETCH NEXT FROM myCursor99 INTO @sql
      END
    
    CLOSE myCursor99
    DEALLOCATE myCursor99
    GO
    
    SELECT * FROM myTable99
    ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Posts
    35
    Thanks...this really helped... I did modify it a bit here is what I did:

    declare @tempName sysname
    declare @sql varchar(8000)

    declare myCursor99 CURSOR
    FOR
    select COLUMN_NAME
    FROM INFORMATION_SCHEMA.Columns
    where TABLE_NAME = 'XYZ'

    open myCursor99

    fetch next from myCursor99 INTO @tempName

    while @@FETCH_STATUS = 0
    Begin
    set @sql = 'select '+''''+@tempName+''''+', count(*) from XYZ '
    +'where '+@tempName+' is null'
    exec (@sql)
    FETCH NEXT FROM myCursor99 INTO @tempName
    END
    CLOSE myCursor99
    DEALLOCATE myCursor99
    "My Idea is that everyone should be required to use small fonts. That way we'll save disk space.
    ~Asok.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "cursors!! foiled again"

    what did pat say the other day? a cursor solution is 100 to 100,000 times as slow as a set-based solution?

    what about something as simple as:
    Code:
    select min(col1), min(col2), ... from thetable
    if min(xxx) returns null, then all values in the entire xxx column are null, eh
    Last edited by r937; 08-30-04 at 19:18.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2003
    Posts
    35
    Id rather just be able to see the amount of nulls.

    Assuming you know how many columns are in the table I think I could use instead of using a cursor.

    declare @counter as int
    set @counter = 1
    declare @tempName as sysname
    declare @sqlString as varchar(8000)

    while @counter <= number of columns
    begin
    set @tempName = COL_NAME(OBJECT_ID('NAME_OF_TABLE'), @counter)
    set @sqlString = 'SELECT '+''''+@tempName+''''+'as Column_Name, COUNT(*) as Num_of_Nulls FROM NAME_OF_TABLE WHERE '+@tempName+' is null'
    exec(@sqlString)
    set @counter = @counter + 1
    end
    "My Idea is that everyone should be required to use small fonts. That way we'll save disk space.
    ~Asok.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •