January 10, 2008 at 4:27 am
Hi
I want to know which tables are my consumers.
I found this T-SQL Code :
use nav_sepia
SET NOCOUNT ON DBCC UPDATEUSAGE(0) -- DB size.
EXEC sp_spaceused-- Table row counts and sizes.
CREATE TABLE #t
( [name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18))
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' SELECT *FROM #t-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]FROM #t DROP TABLE #t
But when I run it I get the following error :
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 2812, Level 16, State 62, Line 11
Could not find stored procedure 'sp_msForEachTable'.
How can this be ? It's an sys sp ??
Anyone got a clue ?
I'm running in the management studio
Thanks in advance !!!
Kind regards
El JEfe
JV
January 10, 2008 at 4:56 am
This is happening because you do not have stored procedure sp_msForEachTable in your database nav_sepia to check this execute following query to your database nav_sepia.
select * from sysobjects where name = 'sp_MSforeachtable'
result will be no record found.
(0 row(s) affected)
Now do the following steps:
Step:1 Execute following command
use master
go
sp_helptext 'sp_MSforeachtable'
Step:2
copy the result set and run the script against database nav_sepia
Step:3
Now run your original script
use nav_sepia
SET NOCOUNT ON DBCC UPDATEUSAGE(0) -- DB size.
EXEC sp_spaceused-- Table row counts and sizes.
CREATE TABLE #t
( [name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18))
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' SELECT *FROM #t-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]FROM #t DROP TABLE #t
This time it will be successful.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
January 10, 2008 at 5:25 am
Hi thx for your answer
When I try to create the sp, I got following result :
Msg 2760, Level 16, State 1, Procedure sp_MSforeachtable, Line 27
The specified schema name "sys" either does not exist or you do not have permission to use it.
I'm administrator, so... ?
Thanks in advance
El Jefe
JV
March 24, 2011 at 2:52 am
Hi,
Probably your database is case-sensitive.
Try sp_MSforeachtable instead of sp_msForEachTable
So, only MS in capital letters.
Pieter
November 24, 2011 at 6:27 am
Hi,
Even after changing the name to MS it is not working. does it need any spl administrator rights?
November 24, 2011 at 6:41 am
Alternate suggestion. Stop fighting with ForEachTable (which, by the way you don't need to create in user databases, it's a system procedure accessible from anywhere) and use this DMV
sys.dm_db_index_physical_stats (See Books Online for details)
You can get the page count for all tables and indexes in the database (index_id 0 or 1 for the table, 2+ for the nonclustered indexes). Multiply page count by 8192 to get the size in bytes (divide by 1024 for kB, divide by 1024 again for MB)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 28, 2011 at 12:52 pm
Try the following from a query window on the database in question
create table #temp(tablename varchar(120), reccount int)
go
sp_msforeachtable 'insert #temp select ''?'',count(*) from ?'
select * from #temp
November 28, 2011 at 11:25 pm
john.campbell-1020429 (11/28/2011)
Try the following from a query window on the database in questioncreate table #temp(tablename varchar(120), reccount int)
go
sp_msforeachtable 'insert #temp select ''?'',count(*) from ?'
select * from #temp
Congratulations, you've just done a table scan on every table in the database thereby generating massive amounts of IO.
Sql Server maintains row counts in the sys.partitions DMV, so this query is basically free:
create table #temp(tablename sysname, reccount int)
insert into #temp
select t.name,
sum(rows)
from sys.tables t inner join sys.partitions p
on t.object_id = p.object_id
where p.index_id in (0,1)
group by t.name
November 28, 2011 at 11:47 pm
Apart from this there is another alternative, i.e., from GUI.
Right click on database-->click on Reports-->click on standard Reports-->Click on Disk usage by Tables.
It will generate a report and gives the details for all tables in that database.
November 29, 2011 at 9:17 am
Here's another:
SELECT substring(o.name,1,50) as 'Table Name',
ddps.row_count , used_page_count, (used_page_count * 8)/1024 as Mbytes--, *
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0 -- 1 for system databases DB2 MSDB=2983.88 MB, 203.88 MB free
and ddps.row_count >=1
ORDER BY used_page_count desc
November 29, 2011 at 9:57 am
Here are a few that I like to use
http://jasonbrimhall.info/2011/11/17/table-space-revised-again/
http://jasonbrimhall.info/2011/11/21/table-space-cs-part-deux/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply