Solved

sorting within sp_MSforeachdb

Posted on 2010-08-20
6
786 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 27 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 58

Accepted Solution

by:
cyberkiwi earned 473 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 473 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now