?
Solved

sorting within sp_MSforeachdb

Posted on 2010-08-20
6
Medium Priority
?
872 Views
Last Modified: 2012-05-10
is it possible to order the result of the data from within the sp_MSforeachdb in the attached code (thanks Scott), to be sorted by the count(*)?

example:
Database1 432
Database2 323
Database3 234 (Descending order)

thanks
EXEC sp_MSforeachdb '
IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'')
    RETURN
DECLARE @tableCount int
SELECT @tableCount = COUNT(*)
FROM [?].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE''
--...other conditions if you want them...
PRINT ''?, TableCount = '' + CAST(@tableCount AS varchar(10))
'

Open in new window

0
Comment
Question by:anushahanna
  • 4
6 Comments
 
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 108 total points
ID: 33489412
Personally i would say not. but you could save results to a new db then do sorts etc there (its what i do)
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33489452
Not without a temp table

if OBJECT_ID('tempdb..#dbtablecount') is not null drop table #dbtablecount;
create table #dbtablecount (db sysname, tblcount int);
EXEC sp_MSforeachdb '
INSERT #dbtablecount (db, tblcount)
SELECT ''?'', COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE''
  AND NOT ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'')
--...other conditions if you want them...
--PRINT ''?, TableCount = '' + CAST(@tableCount AS varchar(10))
';
declare @print varchar(max)
select @print=coalesce(@print+char(13)+char(10),'')
      + db + ', TableCount = ' + CAST(tblcount AS varchar(10))
from #dbtablecount;
drop table #dbtablecount;
print @print;
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33489475
Correction


if OBJECT_ID('tempdb..#dbtablecount') is not null drop table #dbtablecount;
create table #dbtablecount (db sysname, tblcount int);
EXEC sp_MSforeachdb '
IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'')
    RETURN
INSERT #dbtablecount (db, tblcount)
SELECT ''?'', COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE''
--...other conditions if you want them...
--PRINT ''?, TableCount = '' + CAST(@tableCount AS varchar(10))
';
declare @print varchar(max)
select @print=coalesce(@print+char(13)+char(10),'')
      + db + ', TableCount = ' + CAST(tblcount AS varchar(10))
from #dbtablecount
order by tblcount;
drop table #dbtablecount;
print @print;
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 58

Accepted Solution

by:
cyberkiwi earned 1892 total points
ID: 33489484
Putting things back from the original query..


if OBJECT_ID('tempdb..#dbtablecount') is not null drop table #dbtablecount;
create table #dbtablecount (db sysname, tblcount int);
EXEC sp_MSforeachdb '
IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'')
    RETURN
INSERT #dbtablecount (db, tblcount)
SELECT ''?'', COUNT(*)
FROM [?].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE''
--...other conditions if you want them...
--PRINT ''?, TableCount = '' + CAST(@tableCount AS varchar(10))
';
declare @print varchar(max)
select @print=coalesce(@print+char(13)+char(10),'')
      + db + ', TableCount = ' + CAST(tblcount AS varchar(10))
from #dbtablecount
order by tblcount;
drop table #dbtablecount;
print @print;
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 1892 total points
ID: 33489494
For SQL Server 2000 (if the number of databases is ~> 200, which makes @print greater than the limit of varchar(8000).
set nocount on;
if OBJECT_ID('tempdb..#dbtablecount') is not null drop table #dbtablecount;
create table #dbtablecount (db sysname, tblcount int, rank int);
EXEC sp_MSforeachdb '
IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'')
    RETURN
INSERT #dbtablecount (db, tblcount)
SELECT ''?'', COUNT(*)
FROM [?].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE''
--...other conditions if you want them...
--PRINT ''?, TableCount = '' + CAST(@tableCount AS varchar(10))
';

update #dbtablecount
set rank=(select COUNT(*) from #dbtablecount b
	where #dbtablecount.tblcount>b.tblcount or
	(#dbtablecount.tblcount=b.tblcount and #dbtablecount.db>=b.db))
--select * from #dbtablecount order by tblcount

declare @print varchar(8000)
declare @max int select @max = MAX(rank) from #dbtablecount
declare @rank int set @rank=1
while @rank <= @max
begin
select @print= db + ', TableCount = ' + CAST(tblcount AS varchar(10))
from #dbtablecount
where rank=@rank;
print @print;
set @rank=@rank+1;
end;

drop table #dbtablecount;

Open in new window

0
 
LVL 6

Author Comment

by:anushahanna
ID: 33489564
that was phenomenal. Thanks Richard!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

592 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question