|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
query helpI 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 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 Show quote "Hassan" <has***@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... Show quote "Ben Nevarez" <bneva***@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" <has***@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 >> > > Hassan (has***@hotmail.com) writes:
> I need a query to give me dbname, tablename,rowcount,size for all user dbs sp_MSforeachdb 'SELECT db = ''?'', "table" = object_name(id), rows, size = > and tables on a server > > I know i can use sp_spaceused.. But want a query that I can then run on > any server 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, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Erland,
It lists the same tablenames for all databases :-( Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns971BB51D75D5Yazorman@127.0.0.1... > Hassan (has***@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, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hassan (has***@hotmail.com) writes:
> It lists the same tablenames for all databases :-( Oops! Change "sysindexes" to "?.dbo.sysindexes", and it should workbetter. The ? is a placeholder for the DB name in sp_MSforeachdb. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics