May 23, 2003 at 7:24 am
is there a command like sp_helpdb that will show the size of all the tables in a particular database?
May 23, 2003 at 8:09 am
EXEC sp_MSforeachtable 'sp_spaceused ''?'''
May 23, 2003 at 8:11 am
I don't know if there is a builtin command for what you want.
I use this one:
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
I've created it in Master and call it from QA with the appropriate db chosen
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 23, 2003 at 8:13 am
Hi Allen,
quote:
EXEC sp_MSforeachtable 'sp_spaceused ''?'''
I wasn't aware of this. Thanks!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 23, 2003 at 11:21 am
ok a5xo3z1, do i have to create a stored procedure for that? is creating procedures hard or should i just learn to do them? can you tell me how to get what you gave me to show me output?
May 26, 2003 at 12:34 am
Hi matt1,
basically, you have to cut and paste the whole code into QA, select the correct db (in my case master) from the combobox and hit F5 to run the code. You should get a response like "Commands completed successfully". To check you can see in EM in your db a stored proc named sp_rpttblspc. In fact you could have also created the proc using EM by selecting to Stored Procedure Entry of your db and then right-click in your right window. Select New Stored Procedure and paste the code there. Both ways do the same. To call the proc go back to QA, select the db you want to check and type EXEC sp_rpttblspc. And once again F5. You should get a resultset for each User table in the chosen db.
The guy who created the code is a SQL Server MVP with a nice own website. I believe the link to is somewhere on this site.
Creating Stored Procs isn't very hard, is it?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 27, 2003 at 5:58 am
no its not but how to you view them in EM?
May 27, 2003 at 6:01 am
Hi matt1,
in EM expand your db, click on the Stored Procedure entry and from the list on the right choose your procedure. Double the procedure to see the content.
Cheers,
Frank
Edited by - a5xo3z1 on 05/27/2003 06:02:22 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 27, 2003 at 6:06 am
Hi matt1,
quote:
no its not but how to you view them in EM?
case you've created it in master like me, maybe you have to make master 'visible' by right-clicking on SQL Server registration and checking 'Show system databases and system objects'
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 27, 2003 at 6:08 am
ah, yes, i created it in one database only, not in master. for now i want to use it in just one database. this is a great stored procedure, thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply