September 17, 2008 at 9:15 am
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
September 17, 2008 at 9:27 am
How about this:
[font="Courier New"]SELECT
*
FROM
INFORMATION_SCHEMA.Columns
WHERE
is_nullable = 'YES'[/font]
Jack Corbett
Consultant - Straight Path Solutions
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
September 17, 2008 at 9:36 am
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
September 17, 2008 at 9:55 am
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
Consultant - Straight Path Solutions
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
September 17, 2008 at 10:00 am
Yes, exactly!
September 17, 2008 at 3:39 pm
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:
[font="Courier New"]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[/font]
Jack Corbett
Consultant - Straight Path Solutions
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
September 18, 2008 at 7:37 am
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
September 18, 2008 at 7:59 am
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
[font="Courier New"]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[/font]
Jack Corbett
Consultant - Straight Path Solutions
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
September 18, 2008 at 9:35 am
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
September 18, 2008 at 9:55 am
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
Consultant - Straight Path Solutions
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
September 18, 2008 at 11:42 am
Thanks Jack,
I think I can make this work. The NULL column is always 0 because of the concatenation (I guess) but I am only concerned about the non-null fields.
When I try to run the resulting query, I get the following error:
Msg 279, Level 16, State 2, Line 1
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
Any ideas?
Thanks again,
Wes
September 18, 2008 at 11:50 am
I see why I am getting the error, but do you know how I can cast those datatypes dynamically in the cursor?
Thanks,
Wes
September 18, 2008 at 11:58 am
I found there was only one column in this DB, so I excluded it in the cursor until I figure something else out.
But now I get this error:
Msg 8621, Level 17, State 1, Line 1
Internal Query Processor Error: The query processor ran out of stack space during query optimization.
Any help would be very appreciated!
Thanks,
Wes
September 18, 2008 at 12:17 pm
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply