Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Number of Non NULL Fields in a database Expand / Collapse
Author
Message
Posted Wednesday, September 17, 2008 9:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 7:24 AM
Points: 170, Visits: 45
I have looked through the metabase views but have not gotten a satisfactory result as of yet.

Please provide any help, it would be very appreciated.

Thanks,
Wes



Post #571087
Posted Wednesday, September 17, 2008 9:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 10,295, Visits: 13,281
How about this:

SELECT 
  
*
FROM  
  
INFORMATION_SCHEMA.Columns
WHERE
  
is_nullable = 'YES'






Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #571100
Posted Wednesday, September 17, 2008 9:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 7:24 AM
Points: 170, Visits: 45
Thanks for the reply Jack,

I am looking for the actual number of fields that are not Null in every table in a database.

For example, I will need to find the number of Columns times the number of Rows in a table, that will give me the total number of all fields in a table. Then I need to subtract the number of Null fields.

I would need to do this for each table.

Thanks,
Wes



Post #571114
Posted Wednesday, September 17, 2008 9:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 10,295, Visits: 13,281
So you want to know how many non-null values are stored in each column in each table and how many total rows in the table?

For example I have tableA(id int PK, col1 varchar(10) Not Null, col2 Varchar(10) Null) with 10 rows. You want a return something like this:

TableName   RowCount   ColumnName   Nulls   Non-Nulls
---------- --------- ------------ ---- ---------
tableA 10 id 0 10
tableA 10 Col1 0 10
tableA 10 Col2 3 7





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #571130
Posted Wednesday, September 17, 2008 10:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 7:24 AM
Points: 170, Visits: 45
Yes, exactly!


Post #571135
Posted Wednesday, September 17, 2008 3:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 10,295, Visits: 13,281
How many tables/columns are we talking about? Do you have some off-peak time to do this in?

This may not be the "best" or "right" way to get his information, but this code will generate the code you need to get the information. I really don't like this solution, but I can't think of another way to do it. Hopefuly someone else can:

DECLARE @table TABLE(table_name VARCHAR(255), column_name VARCHAR(255), null_count INT, non_null_count INT, total_count INT)

DECLARE @sql VARCHAR(MAX), @final_sql VARCHAR(MAX)

DECLARE c_cols CURSOR FOR
   SELECT
      
'Select ''' + table_name + ''' as table_name, ''' + column_name + ''' as column_name, (Select Count([' + column_name + ']) as rows From ' + table_schema + '.' + table_name + ' Where [' + column_name + '] Is Null) as nulls,
(Select Count(['
+ column_name + ']) as rows From ' + table_schema + '.' + table_name + ' Where [' + column_name + '] Is Not Null) as non_nulls,
(Select Count(['
+ column_name + ']) as rows From ' + table_schema + '.' + table_name + ') as total' AS sql
  
FROM  
      
INFORMATION_SCHEMA.Columns
  
WHERE
      
Table_Name LIKE 'a%'
OPEN c_cols

FETCH Next FROM c_cols INTO @sql

WHILE @@FETCH_STATUS = 0
  
BEGIN
       SET
@final_sql = ISNULL(@final_sql, '') + @sql + ' Union All '

      
FETCH Next FROM c_cols INTO @sql
  
END

CLOSE
c_cols
DEALLOCATE c_cols

PRINT @final_sql





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #571354
Posted Thursday, September 18, 2008 7:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 7:24 AM
Points: 170, Visits: 45
One of the DB's I'm looking at has 188 tables with 2516 columns. Yes I can run during off peak times, but it is actually a replicated Oracle DB, so this DB is for reporting purposes only.

When I try to run this, I get the following errors,

Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'max'.
Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'max'.
Msg 137, Level 15, State 1, Line 16
Must declare the variable '@sql'.
Msg 137, Level 15, State 2, Line 20
Must declare the variable '@final_sql'.
Msg 137, Level 15, State 1, Line 22
Must declare the variable '@sql'.
Msg 137, Level 15, State 2, Line 28
Must declare the variable '@final_sql'.

I looked at BOL and the syntax looks correct.

Thanks again for any help you can supply.
Wes



Post #571780
Posted Thursday, September 18, 2008 7:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 10,295, Visits: 13,281
Are you using SQL 2005? Based on what the errors are I'm guessing you are running SQL 2000 since varchar(max) was added in 2005. Change the max to 8000. This probably means you will need to batch the process. So I'd dump the column sql data into a table with an identity column and run the cursor off that and either have a processed flag or delete the rows as I process them. Something like

IF OBJECT_ID('dbo.column_sql') IS NULL
  
BEGIN
       CREATE TABLE
dbo.column_sql
          
(
          
id INT IDENTITY(1,1) PRIMARY KEY,
          
sql_statement VARCHAR(8000)
           )
  
END
ELSE
   BEGIN
       TRUNCATE TABLE
dbo.column_sql
  
END

DECLARE
@sql VARCHAR(8000), @final_sql VARCHAR(8000), @id INT

INSERT INTO
dbo.column_sql
  
(
  
sql_statement
  
)
  
SELECT
        
'Select ''' + table_name + ''' as table_name, ''' + column_name + ''' as column_name, (Select Count([' + column_name + ']) as rows From ' + table_schema + '.' + table_name + ' Where [' + column_name + '] Is Null) as nulls,
                (Select Count(['
+ column_name + ']) as rows From ' + table_schema + '.' + table_name + ' Where [' + column_name + '] Is Not Null) as non_nulls,
                (Select Count(['
+ column_name + ']) as rows From ' + table_schema + '.' + table_name + ') as total' AS sql
  
FROM  
      
INFORMATION_SCHEMA.Columns


WHILE EXISTS (SELECT * FROM dbo.column_sql)
  
BEGIN
       DECLARE
c_sql CURSOR FOR
           SELECT TOP
100
               id
,
              
sql_statement
          
FROM
              
dbo.column_sql
          
ORDER BY
              
id

      
OPEN c_sql
      
      
FETCH Next FROM c_sql INTO
          
@id,
          
@sql
          
      
WHILE @@FETCH_STATUS = 0
          
BEGIN
               PRINT
' IN Fetch Loop'
              
SET @final_sql = ISNULL(@final_sql, '') + @sql + ' Union All '

              
DELETE
               FROM
                  
dbo.column_sql
              
WHERE
                  
id = @id
          
              
PRINT 'Row deleted'

              
FETCH Next FROM c_sql INTO
                  
@id,
                  
@sql
          
END
          
           SELECT
@final_sql
          
CLOSE c_sql
          
DEALLOCATE c_sql
  
END






Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #571794
Posted Thursday, September 18, 2008 9:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 7:24 AM
Points: 170, Visits: 45
SQL Server 2005, once I changed the max to 8000 it runs without error. But of course, it only gets halfway throught the first 2 tables.

The query is running against a SQL Server 2000 instance though, I didn't think about it before. I new max was new to 2005.

I'm not sure what this is doing. It looks like every "select @final_sql" is selecting the same table and column code.

Is this what it is suppose to do? Or do I need to add some code to make it work?

Thanks again,
Wes



Post #571893
Posted Thursday, September 18, 2008 9:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 10,295, Visits: 13,281
I forgot to reset the @final_sql variable so it was continually adding on. Down where you close the cursor put in
Set @final_sql = ''

. You may also want to check the length of the variable and when it gets to 8000 kick out as well. Something like Len(@final_sql + @sql) < 8000 added to the @@FETCH_STATUS.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #571922
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse