April 8, 2009 at 5:58 pm
I guess that script should be smth like that:
If exists (select * from TEMPDB.sys.sysobjects where id = object_id(N'[TEMPDB].[dbo].[#tbl_info]'))
Drop table [#tbl_info]
Create Table #tbl_info (
tblName sysname,
NumberOfRows int,
ReservedSpace varchar(24),
DataSpace varchar(24),
IndexSize varchar(24),
UnusedSpace varchar(24)
)
Declare tbl_cur cursor for select [name] from sys.objects where type = 'U'
Declare @tblName sysname
Open tbl_cur
Fetch next from tbl_cur into @tblName
While @@fetch_status = 0
Begin
Insert into #tbl_info
Exec sp_spaceused @tblName
Fetch next from tbl_cur into @tblName
End
Close tbl_cur
Deallocate tbl_cur
Select * from #tbl_info order by
convert(int, replace(DataSpace,'KB','')) desc
April 8, 2009 at 9:16 pm
Pile On!
And here's another one! This ones a View based on SQL Server 2005 System catalogs:
/*
vwTableInfo - Table Information View
This view display space and storage information for every table in a
SQL Server 2005 database.
Columns are:
Schema
Name
Ownermay be different from Schema)
Columnscount of the max number of columns ever used)
HasClusIdx1 if table has a clustered index, 0 otherwise
RowCount
IndexKBspace used by the table's indexes
DataKBspace used by the table's data
16-March-2008, RBarryYoung@gmail.com
31-January-2009, Edited for better formatting
*/
--CREATE VIEW vwTableInfo
-- AS
SELECT SCHEMA_NAME(tbl.schema_id) as [Schema]
, tbl.Name
, Coalesce((Select pr.name
From sys.database_principals pr
Where pr.principal_id = tbl.principal_id)
, SCHEMA_NAME(tbl.schema_id)) as [Owner]
, tbl.max_column_id_used as [Columns]
, CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusIdx]
, Coalesce( (Select sum (spart.rows) from sys.partitions spart
Where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]
, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
Where i.object_id = tbl.object_id )
, 0.0) AS [IndexKB]
, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
Where i.object_id = tbl.object_id)
, 0.0) AS [DataKB]
, tbl.create_date, tbl.modify_date
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and idx.index_id < 2)
INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type='E')
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 9, 2009 at 1:14 am
Hi,
You can use this query to get the tablename and rowcount of a database
select so.name, si.rowcnt from sysobjects so
join sysindexes si on so.id = si.id and so.type = 'U'
April 9, 2009 at 1:33 am
Thanks,
but got multiple rows for tables so changed it slightly:
SELECT table_name,table_rows FROM (
select so.name as table_name, si.rowcnt as table_rows,ROW_NUMBER() OVER (PARTITION BY SO.NAME ORDER BY si.rowcnt DESC) AS RC
from sysobjects so
join sysindexes si on so.id = si.id and so.type = 'U'
)F WHERE RC = 1
There seem to be a lot of very swift folutions to this question.
Thanks.
Mark.
April 9, 2009 at 1:42 am
Ells (4/9/2009)
Thanks,but got multiple rows for tables so changed it slightly:
SELECT table_name,table_rows FROM (
select so.name as table_name, si.rowcnt as table_rows,ROW_NUMBER() OVER (PARTITION BY SO.NAME ORDER BY si.rowcnt DESC) AS RC
from sysobjects so
join sysindexes si on so.id = si.id and so.type = 'U'
)F WHERE RC = 1
The way to fix that so that you only get row row per table is thus:
select so.name as table_name, si.rowcnt as table_rows
from sysobjects so
join sysindexes si on so.id = si.id
WHERE so.type = 'U' AND si.index_id in (0,1)
Without the filter on index ID you'll get all the nonclustered indexes as well. Filter on cluster or heap and you'll get 1 row per table.
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
April 9, 2009 at 6:52 am
quick and dirty - but handy for ad-hoc use:
Set query output to text
sp_MSforeachtable @command1=" SELECT '?', COUNT(*) FROM ?"
April 11, 2009 at 12:11 pm
mosaic (4/9/2009)
quick and dirty - but handy for ad-hoc use:Set query output to text
sp_MSforeachtable @command1=" SELECT '?', COUNT(*) FROM ?"
Um... maybe. If you have tables like what are in my system, that'll take about a week.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 15, 2009 at 12:57 pm
wow over 20 answers to this guys guestion "lol"
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply