query help

I need a query to give me dbname, tablename,rowcount,size for all user dbs
and tables on a server
I know i can use sp_spaceused.. But want a query that I can then run on any
server
ThanksIf you are using SQL Server 2005, select your database, then Summary,
Report, Disk Usage and expand Disk Space Used by Tables.
Ben Nevarez, MCDBA, OCP
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23yeA8Dl8FHA.740@.TK2MSFTNGP11.phx.gbl...
>I need a query to give me dbname, tablename,rowcount,size for all user dbs
>and tables on a server
> I know i can use sp_spaceused.. But want a query that I can then run on
> any server
> Thanks
>|||I was looking for the TSQL and I am using SQL 2000
I want the result to look like
DBName TableName Size(KB)
DB1 T1 1200
DB1 T2 100
DB2 T1 50
and so on...
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:%236fY%23Km8FHA.1188@.TK2MSFTNGP12.phx.gbl...
> If you are using SQL Server 2005, select your database, then Summary,
> Report, Disk Usage and expand Disk Space Used by Tables.
> Ben Nevarez, MCDBA, OCP
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23yeA8Dl8FHA.740@.TK2MSFTNGP11.phx.gbl...
>|||Hassan (hassan@.hotmail.com) writes:
> I need a query to give me dbname, tablename,rowcount,size for all user dbs
> and tables on a server
> I know i can use sp_spaceused.. But want a query that I can then run on
> any server
sp_MSforeachdb 'SELECT db = ''?'', "table" = object_name(id), rows, size =
reserved * 8192 / 1000000 from sysindexes WHERE indid IN (0,1) ORDER BY 2'
You will get one result set per database.
Note that the procedure sp_MSforeachdb is undocumented and not supported.
(But it stilll works in SQL 2005; I just tested the above in SQL 2005.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
It lists the same tablenames for all databases :-(
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns971BB51D75D5Yazorman@.127.0.0.1...
> Hassan (hassan@.hotmail.com) writes:
> sp_MSforeachdb 'SELECT db = ''?'', "table" = object_name(id), rows, size =
> reserved * 8192 / 1000000 from sysindexes WHERE indid IN (0,1) ORDER BY 2'
> You will get one result set per database.
> Note that the procedure sp_MSforeachdb is undocumented and not supported.
> (But it stilll works in SQL 2005; I just tested the above in SQL 2005.)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hassan (hassan@.hotmail.com) writes:
> It lists the same tablenames for all databases :-(
Oops! Change "sysindexes" to "?.dbo.sysindexes", and it should work
better.
The ? is a placeholder for the DB name in sp_MSforeachdb.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx