Solved

sorting within sp_MSforeachdb

Posted on 2010-08-20
6
799 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

840 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