Removed Null values by this output

Question

Post reply

Removed Null values by this output

SQL Galaxy

One Orange Chip

Points: 29472
More actions
August 27, 2011 at 4:24 am

#256270

Hi,
This script output display total No.of table, SP, PK and etc.. please suggestion me, how to remove null value both column type and type desc
SELECT TYPE, NoOfObject=COUNT(*),type_desc
FROM sys.objects
GROUP BY ROLLUP(TYPE,type_desc)
ORDER BY TYPE,type_desc
output
TYPENoOfObjecttype_desc
NULL175NULL
D 31NULL
D 31DEFAULT_CONSTRAINT
FN4NULL
FN4SQL_SCALAR_FUNCTION
IT5NULL
IT5INTERNAL_TABLE
P 36NULL
P 36SQL_STORED_PROCEDURE
PK21NULL
PK21PRIMARY_KEY_CONSTRAINT
S 45NULL
S 45SYSTEM_TABLE
SQ3NULL
SQ3SERVICE_QUEUE
U 29NULL
U 29USER_TABLE
UQ1NULL
UQ1UNIQUE_CONSTRAINT
thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It's because you have the rollup over two columns. This probably does what you want.

SELECT MAX(type) AS Type, COUNT(*) AS NoOfObject, type_desc

FROM sys.objects

GROUP BY ROLLUP(type_desc)

ORDER BY MAX(type), type_desc

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

thanks Gila for reply..

Could you tell me, again one null values this output in column of type_desc.

what is this excatly mention database?

TypeNoOfObjecttype_desc

D 31DEFAULT_CONSTRAINT

FN4SQL_SCALAR_FUNCTION

IT5INTERNAL_TABLE

P 36SQL_STORED_PROCEDURE

PK21PRIMARY_KEY_CONSTRAINT

S 45SYSTEM_TABLE

SQ3SERVICE_QUEUE

U 29USER_TABLE

UQ175NULL

UQ1UNIQUE_CONSTRAINT

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

That's your grand total.

This should be clearer

SELECT CASE GROUPING(type_desc) WHEN 1 THEN 'Total' ELSE MAX(type) END AS Type, COUNT(*) AS NoOfObject, type_desc

FROM sys.objects

GROUP BY ROLLUP(type_desc)

ORDER BY GROUPING(type_desc), MAX(type), type_desc

That's what RollUp does, computes total at all grouping levels. so you get the count for each, then the overall count.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

Thank you so much....

Now query result is cleared & also my doubut.