Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 858
  • Last Modified:

sorting within sp_MSforeachdb

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
anushahanna
Asked:
anushahanna
  • 4
3 Solutions
 
James MurrellProduct SpecialistCommented:
Personally i would say not. but you could save results to a new db then do sorts etc there (its what i do)
0
 
cyberkiwiCommented:
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
 
cyberkiwiCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
cyberkiwiCommented:
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
 
cyberkiwiCommented:
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
 
anushahannaAuthor Commented:
that was phenomenal. Thanks Richard!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now