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 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy