October 10, 2007 at 7:40 am
Hi All,
I am using the below SP to find out COUNT of datum for Number of Null Data and Number of Not Null Data for a given table.
The script works fine, but if the columns more then it wouldn't take all the columns as the VARCHAR exceeds it's MAX capacity. I need to know if there is any alternative in this regard.
DECLARE @sql VARCHAR(8000)
SELECT @sql = ''
SELECT @sql = @sql + ' UNION ALL SELECT ' + QUOTENAME(COLUMN_NAME, '''')
+ CASE WHEN ORDINAL_POSITION = 1 THEN ' AS ColumnName,' ELSE ',' END
+ CONVERT(VARCHAR, ORDINAL_POSITION)
+ CASE WHEN ORDINAL_POSITION = 1 THEN ' AS Position,' ELSE ',' END
+ 'COUNT(' + QUOTENAME(COLUMN_NAME) + ')'
+ CASE WHEN ORDINAL_POSITION = 1 THEN ' AS NotNullData,' ELSE ',' END
+ 'COUNT(*)'
+ CASE WHEN ORDINAL_POSITION = 1 THEN ' AS TotalData' ELSE '' END
+ ' FROM ' + QUOTENAME(TABLE_NAME)
FROM (
SELECT TOP 100 PERCENT
TABLE_NAME,
COLUMN_NAME,
ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblFriends'
ORDER BY ORDINAL_POSITION
) AS d
SELECT @sql = STUFF(@SQL, 1, 11, 'SELECT ColumnName, TotalData - NotNullData AS NullData, NotNullData AS NotNullData, TotalData FROM ('),
@sql = @sql + ') AS d ORDER BY Position'
--PRINT @sql
EXEC (@SQL)
October 10, 2007 at 7:46 am
You can't use a text variable, but you could try breaking this up. Maybe going with the top 100, then looping through successive groups of 100.
However, I don't understand what you're trying to do. The sproc seems to be trying to do a report, which isn't a great use of SQL. Maybe we can help you do this another way if you give us an idea of what you're trying to accomplish.
October 10, 2007 at 8:01 am
You can create dynamic SQL that is over 8000 characters on 2005. Example:
DECLARE @query AS nvarchar(MAX), @i AS int
SELECT @query = N'', @i = 0
WHILE @i < 500
BEGIN
SELECT @query = @query + N'PRINT ''Long statement''', @i=@i+1
END
EXEC sp_executesql @query
In this case I'm executing something over 11000 characters. But I agree with Steve that there is probably a much better alternative if we just make one step back from this problem 🙂
Andras
October 10, 2007 at 8:05 am
Hi Steve,
Thank you for the reply,
I need to find out in an given table, I need the count of individual columns having actual data and how many columns have null.
For Eg.: Table1
ColA | Col B | ColN
Row1 X | | X
Row2 | X |
Row3 X | X |
Row4 | X |
For Table1, I require report like this.
Total Null | Total Not Null | Total Data
ColA 2 | 2 | 4
ColB 1 | 3 | 4
ColN 3 | 1 | 4
This is working fine if the number of column is less (ColA, ColB, ColN)
My table has more than 150 columns so in the above SP the variable @sql VARCHAR can accomidate only MAX 8000 characters, as my columns 150 the VARCHAR over flows part of the last columns count I am not getting.
Thanks,
San
October 10, 2007 at 6:06 pm
I shudder to even imagine what you would need that for. The management report is going to be more torture on the system than the system will generate for itself...
If you're in 2005 - read up on the UNPIVOT option (it will "flip" columns into rows for you). Unpivot the below query.
If you're not - replicate this type of union and have something like excel "flip it" for you. It's a LOT more efficient than your code, doesn't hammer the server, and is a lot more efficient to write (577 characters in your vs 367 in mine)
declare @t bigint
select @t=count(*) from spt_values
select 'nulls',count(name) name,count(number) number,count(type) type,count(low) low, count(high) high, count(status) status from spt_values
union all
select 'notnulls',@t-count(name),@t-count(number),@t-count(type),@t-count(low),@t-count(high),@t-count(status) from spt_values
union all
select 'total',@t,@t,@t,@t,@t,@t
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 11, 2007 at 12:50 am
Hi Matt,
Thank you for the reply,
It's on SQL Sqerver 7 not on the SQL Server 2005
Cheers,
San
October 11, 2007 at 12:11 pm
Well - you could always use the second syntax I'm throwing at you.
Otherwise, here are a few "space-saving tips":
1. The ONLY select in the long sequence of UNION ALL select requiring the "AS <Fieldname>" notation is the very first select. All other select will use the aliases defined in the first select: not including that in your syntax would shorten your statement by 54 characters per field in the table (if my count's right).
2. The "Total data" column is the same for ever row in your select. If you just printed it once at the top and didn't include ', count(8) ' for EACH field, a. your database would be much happier for not getting hammered on, and b. you could cut out yet another 11 characters per field.
Yes, this would require some changes to your code a bit.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 17, 2007 at 7:00 am
Plus only counting nullable columns will reduce the size of the query
Could build several strings and then EXEC(@string1+@string2+@string3)
Or use a cursor like this (but performance could be poor due to multi scans of table 🙁 )
DECLARE @sql nvarchar(255)
DECLARE @TABLE_NAME sysname,@COLUMN_NAME sysname,@ORDINAL_POSITION int,@ROWS int
CREATE TABLE #temp (ORDINAL_POSITION int,COLUMN_NAME sysname,nullct int)
SELECT @ROWS = COUNT(*)
FROM [tblFriends]
DECLARE mycurs CURSOR FOR
SELECT TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblFriends'
ORDER BY ORDINAL_POSITION
OPEN mycurs
FETCH NEXT FROM mycurs INTO @TABLE_NAME,@COLUMN_NAME,@ORDINAL_POSITION
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'SELECT '''+CAST(@ORDINAL_POSITION as varchar)+''','''+@COLUMN_NAME+''',COUNT(*) FROM '+QUOTENAME(@TABLE_NAME)+' WHERE '+QUOTENAME(@COLUMN_NAME)+' IS NULL'
INSERT INTO #temp (ORDINAL_POSITION,COLUMN_NAME,nullct)
EXEC(@sql)
FETCH NEXT FROM mycurs INTO @TABLE_NAME,@COLUMN_NAME,@ORDINAL_POSITION
END
CLOSE mycurs
DEALLOCATE mycurs
SELECT COLUMN_NAME,nullct AS [Total Null],@ROWS-nullct AS [Total Not Null],@ROWS AS [Total Data]
FROM #temp
ORDER BY ORDINAL_POSITION ASC
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply