Useful Stored Procedure Question

[S G]

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_MSforeachtab le 'exec sp_spaceused ''?'''
The sp_spaceused I found in the help but where is the
sp_Msforeachtab le? 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!

[Dan Guzman]

Re: Useful Stored Procedure Question

sp_MSforeachtab le 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_MSforeachta ble'

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_SCH EMA.TABLES
WHERE
TABLE_TYPE='BAS E 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(@SqlTem plate, '?', @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.cfm?DepartmentID=800

http://www.sqlserverfaq.com

http://www.mssqlserver.com/faq

-----------------------

"S G" <sgpgpjr@yahoo. ie> wrote in message
news:3f83f349$0 $195$75868355@n ews.frii.net...[color=blue]
>
> 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[/color]
in[color=blue]
> the archives 'exec sp_MSforeachtab le 'exec sp_spaceused ''?'''
> The sp_spaceused I found in the help but where is the
> sp_Msforeachtab le? 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![/color]