sp_msforeachdb
-
How could i list size of all databases in (MB/GB) in a server.
-
sp_helpdb?
it lists the DBname, DBSize, and some other stats.
-- Cory
-
i know sp_helpdb will give that info but i need only few db's and thier size in a single result set for a report.
-
sp_msforeachdb @command1 = 'use ? select ''?'', name, size*8/1024 as SizeInMB from sysfiles'
You would have to insert this into a temp table and select out of that - or - you could use the following;
SELECT db_name(database_id) AS DatabaseName, sum((size*8)/1024) as 'All db Size' from sys.master_files group by db_name(database_id)
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Mike,
I'm confused, you asked for all databases, then you say a few. What exactly are you trying to do?
You can also grab the query from sp_helpdb and filter out system dbs if you want that.
-
I am using this for table count in each db, is there a way i can do it on whole server for each dabase listing table count by server by table name.
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC
-
Try this. Note, I've updated your join syntax to bring it into the 21st century.
DECLARE @sql VARCHAR(1000)SET @sql = 'SELECT ''?'', so.name, MAX(si.rows)
FROM sysobjects so
JOIN sysindexes si on so.id = si.id
WHERE so.xtype = ''U''
GROUP BY so.name
ORDER BY 2 DESC'
CREATE TABLE #t ([Database] VARCHAR(255), [TableName] VARCHAR(255), [RowCount] INT)
INSERT #t
EXEC sp_msforeachdb @sql
SELECT * FROM #t ORDER BY 1, 3 DESC
DROP TABLE #t
-
Mike Levan (3/16/2009)
How could i list size of all databases in (MB/GB) in a server.Mike Levan (3/16/2009)
I am using this for table count in each db, is there a way i can do it on whole server for each dabase listing table count by server by table name.SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC
Heh... Ok Mike... which one do you really want? 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
using the same script how would i compare 2 identical databases on diffrent servers. I would like to find out if there is any difference in the numbers of tables and the rowcount from both the database.
-
Redgate SQLCompare. You can download a 14-day trial from their website.
-
Google "Open DBDiff" for schema comparisons
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply