June 3, 2003 at 8:44 am
Hi all,
I want to find the used size of the database and unused size of the database. I knew I can do that using 'dbcc showfilestats'. It runs only for the current database. I want to get the information for all the database in that server. I don't want to create a cursor and loop thru the cursor for each database. is there any way I can get the same information from Sqlserver system tables.
Thanks
June 3, 2003 at 9:05 am
Hi dambalaji,
not exactly what you were asking for, but...
I've posted this some days ago, it's a script I found on the homepage of a SQL Server MVP.
CREATE PROCEDURE sp_rpttblspc (@dbname varchar(30) = null, @tblname varchar(31) = null)
as
/********************************************************************************/
/*Created BY :Umachandar Jayachandran(UC)*/
/*Created ON :15 April 1996*/
/*Description:Used TO CREATE a report OF tables & number of rows.*/
/********************************************************************************/
/*Resources :http://www.umachandar.com/resources.htm */
/********************************************************************************/
SET nocount ON
DECLARE @tablename varchar(30), @cmdstr varchar(255)
SELECT @dbname = isnull(@dbname, db_name()), @tblname = isnull(@tblname , '') + '%'
IF db_id(@dbname) IS NULL
BEGIN
raiserror('Invalid DATABASE name was specified.', -1, -1)
return(1)
END
CREATE TABLE #tblspc
(name varchar(30), rows varchar(10), reserved varchar(20), data varchar(20),
index_size varchar(20), unused varchar(20))
exec('declare tbls CURSOR FOR SELECT name FROM "' + @dbname +
'"..sysobjects WHERE type = ''U'' AND name LIKE ''' + @tblname + '''')
OPEN tbls
while('FETCH IS OK' = 'FETCH IS OK')
begin
FETCH next FROM tbls INTO @tablename
IF @@fetch_status < 0 BREAK
SELECT @cmdstr = 'use "' + @dbname + '" EXEC sp_spaceused ''' + @tablename + ''''
INSERT INTO #tblspc exec(@cmdstr)
IF @@error <> 0
BEGIN
DEALLOCATE tbls
raiserror('Fatal error, unable TO obtain space details FOR tables.', -1, -1)
return(1)
END
end
DEALLOCATE tbls
SELECT name AS "Table Name:", rows as "Number OF Rows:" ,
data AS "Data Size", index_size as "Index Size",
reserved AS "Allocated Size"
FROM #tblspc
ORDER BY convert(int, rows) desc, 1
return(0)
GO
You can also try sp_helpdb or sp_databases. I think you have to copy the code and modify it. Each of them uses cursors.
Is this going in your direction?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 3, 2003 at 9:09 am
I wrote an article for SQL Server Magazine that outlined a process to do that. The process gather used and unused space stats and kept them in a database, for reporting purposes.
Here is the link:
http://sqlmag.com/Articles/Index.cfm?ArticleID=26874
Just to let you know, you need to be a subscriber to read it.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
June 3, 2003 at 9:15 am
You can run sp_helpdb without any parameter to know the size of all dbs in your server or also sp_databases. but to know the free size, you will have to query sysfiles in each databases, or execute sp_spaceused but in every database.
You can use sp_MSforeachdb but it creates a cursor for the database, so it is the same that you don-t want to use
June 5, 2003 at 5:22 am
Why not to use simply the sp_spaceused system stored procedure eventually together with the sp_MSforeachdb proc?
Bye
Gabor
June 13, 2003 at 4:03 am
You can use the combination of sp_ msforeachdb and sp_spaceused.
means
sp_foeeachdb sp_spaceused.
This will show u all database size and unallocated size.
Following SP alos help u to find out DB size.
sp_helpdb,
sp_helpfile
Sachin Bhaygude
Sachin Bhaygude
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply