Solved

sorting within sp_MSforeachdb

Posted on 2010-08-20
6
778 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 58

Accepted Solution

by:
cyberkiwi earned 473 total points
Comment Utility
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
Comment Utility
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
Comment Utility
that was phenomenal. Thanks Richard!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

771 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

9 Experts available now in Live!

Get 1:1 Help Now