SEO

Hi all,
I did a search for a stored procedure that would give me the table
sizes and space used for each table within a database and found this in
the archives ‘exec sp_MSforeachtable 'exec sp_spaceused ''?'''
The sp_spaceused I found in the help but where is the
sp_Msforeachtable? How does this work and yes I’m new to this.
The stored procedure is perfect for my needs I just wouldn’t mind
knowing how it works!
Many thanks
Sam



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

sp_MSforeachtable is an undocumented stored procedure. Like all
undocumented functionality, Microsoft may change or remove it in future
releases or service packs so you shouldn't use it in production code.
You can take a look at the proc source using sp_helptext:

USE master
EXEC sp_helptext 'sp_MSforeachtable'

The script below provides similar functionality.

DECLARE @SqlTemplate nvarchar(4000)
SET @SqlTemplate = N'SELECT ''?'' AS TableName, COUNT(*) AS Rows FROM ?'
DECLARE @SqlStatement nvarchar(4000)
DECLARE @TableName nvarchar(261)
DECLARE TableList CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE='BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
OPEN TableList
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TableList INTO @TableName
IF @@FETCH_STATUS = -1 BREAK
SET @SqlStatement = REPLACE(@SqlTemplate, '?', @TableName)
EXEC (@SqlStatement)
END
CLOSE TableList
DEALLOCATE TableList

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"S G" <sgpgpjr@yahoo.ie> wrote in message
news:3f83f349$0$195$75868355@news.frii.net...
>
> Hi all,
> I did a search for a stored procedure that would give me the table
> sizes and space used for each table within a database and found this
in
> the archives 'exec sp_MSforeachtable 'exec sp_spaceused ''?'''
> The sp_spaceused I found in the help but where is the
> sp_Msforeachtable? How does this work and yes I'm new to this.
> The stored procedure is perfect for my needs I just wouldn't mind
> knowing how it works!
> Many thanks
> Sam
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!