Listing Size of all user Databases in SQL2005

Can anyone please tell me of a way of generating a list of all user databases and their size with one command or view?

Thanks,

Refer to Books Online, Topic: sys.database_files

This will return the size as the number of 8K pages, so do the math in your query.

|||sp_helpdb will generate info about the dbname and size|||

select * from sys.master_files

Will also give you useful information.

Listing Size of all user Databases in SQL2005

Can anyone please tell me of a way of generating a list of all user databases and their size with one command or view?

Thanks,

Refer to Books Online, Topic: sys.database_files

This will return the size as the number of 8K pages, so do the math in your query.

|||sp_helpdb will generate info about the dbname and size|||

select * from sys.master_files

Will also give you useful information.

List Table sizes in db by mb?

Is there a way to see the size in mb of each table in a db?
I am trying to troubleshoot a problem I am seeing. I have 2 dbs with the
same tables and very similar data in the tables, yet when I right click on
the db in SQL enterprise manager and select properties the total db mb on 1
is 45 times bigger in mb than the 2nd.
Thx,
Scott BuerkleySee if this helps:
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
Also see DBCC UPDATEUSAGE in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
Is there a way to see the size in mb of each table in a db?
I am trying to troubleshoot a problem I am seeing. I have 2 dbs with the
same tables and very similar data in the tables, yet when I right click on
the db in SQL enterprise manager and select properties the total db mb on 1
is 45 times bigger in mb than the 2nd.
Thx,
Scott Buerkley|||Hi Narayana,
I have used your 'sp_show_huge_tables' Sp and got
Row count Total space used (MB)
2026 -.09
1548 -2.07
on two of my tables. How is this possible '
Thanks.

>--Original Message--
>See if this helps:
>http://vyaskn.tripod.com/sp_show_biggest_tables.htm
>Also see DBCC UPDATEUSAGE in SQL Server Books Online.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in
message
>news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
>Is there a way to see the size in mb of each table in a
db?
>I am trying to troubleshoot a problem I am seeing. I
have 2 dbs with the
>same tables and very similar data in the tables, yet when
I right click on
>the db in SQL enterprise manager and select properties
the total db mb on 1
>is 45 times bigger in mb than the 2nd.
>Thx,
>Scott Buerkley
>
>.
>|||Most probably incorrect info in sysindexes. Did you try DBCC UPDATEUSAGE?
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:bfce01c3ecd2$672940f0$a501280a@.phx.gbl...
> Hi Narayana,
> I have used your 'sp_show_huge_tables' Sp and got
>
> Row count Total space used (MB)
> 2026 -.09
> 1548 -2.07
> on two of my tables. How is this possible '
> Thanks.
>
>
> message
> db?
> have 2 dbs with the
> I right click on
> the total db mb on 1|||Thank You!!
This worked.
Thx,
Scott Buerkley
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OvSsK4M7DHA.1636@.TK2MSFTNGP12.phx.gbl...
> See if this helps:
> http://vyaskn.tripod.com/sp_show_biggest_tables.htm
> Also see DBCC UPDATEUSAGE in SQL Server Books Online.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
> news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> Is there a way to see the size in mb of each table in a db?
> I am trying to troubleshoot a problem I am seeing. I have 2 dbs with the
> same tables and very similar data in the tables, yet when I right click on
> the db in SQL enterprise manager and select properties the total db mb on
1
> is 45 times bigger in mb than the 2nd.
> Thx,
> Scott Buerkley
>
>|||I got a listing of my table sizes and as it turns out none were that large.
I keep investigating and found that the Transaction log file is the file
that is huge. Does anyone have any suggestions on how to fix this? I do
not need to roll back any data. It is all test data so far.
Thx,
Scott Buerkley
"Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> Is there a way to see the size in mb of each table in a db?
> I am trying to troubleshoot a problem I am seeing. I have 2 dbs with the
> same tables and very similar data in the tables, yet when I right click on
> the db in SQL enterprise manager and select properties the total db mb on
1
> is 45 times bigger in mb than the 2nd.
> Thx,
> Scott Buerkley
>|||Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
news:uFWhAaN7DHA.3804@.tk2msftngp13.phx.gbl...
> I got a listing of my table sizes and as it turns out none were that
large.
> I keep investigating and found that the Transaction log file is the file
> that is huge. Does anyone have any suggestions on how to fix this? I do
> not need to roll back any data. It is all test data so far.
> Thx,
> Scott Buerkley
> "Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
> news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
the
on
on
> 1
>|||This helped.
Thank You!!!
Scott Buerkley
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23wY299N7DHA.1460@.tk2msftngp13.phx.gbl...
> Check out below KB articles:
> INF: How to Shrink the SQL Server 7.0 Transaction Log
> http://support.microsoft.com/defaul...kb;en-us;256650
> INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
> http://support.microsoft.com/defaul...kb;en-us;272318
> Log File Grows too big
> http://www.support.microsoft.com/?id=317375
> Log file filling up
> http://www.support.microsoft.com/?id=110139
> Considerations for Autogrow and AutoShrink
> http://www.support.microsoft.com/?id=315512
> http://www.mssqlserver.com/faq/logs-shrinklog.asp
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
> news:uFWhAaN7DHA.3804@.tk2msftngp13.phx.gbl...
> large.
do
> the
click
> on
> on
>

List Table sizes in db by mb?

Is there a way to see the size in mb of each table in a db?
I am trying to troubleshoot a problem I am seeing. I have 2 dbs with the
same tables and very similar data in the tables, yet when I right click on
the db in SQL enterprise manager and select properties the total db mb on 1
is 45 times bigger in mb than the 2nd.
Thx,
Scott BuerkleySee if this helps:
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
Also see DBCC UPDATEUSAGE in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
Is there a way to see the size in mb of each table in a db?
I am trying to troubleshoot a problem I am seeing. I have 2 dbs with the
same tables and very similar data in the tables, yet when I right click on
the db in SQL enterprise manager and select properties the total db mb on 1
is 45 times bigger in mb than the 2nd.
Thx,
Scott Buerkley|||Hi Narayana,
I have used your 'sp_show_huge_tables' Sp and got
Row count Total space used (MB)
2026 -.09
1548 -2.07
on two of my tables. How is this possible '
Thanks.
>--Original Message--
>See if this helps:
>http://vyaskn.tripod.com/sp_show_biggest_tables.htm
>Also see DBCC UPDATEUSAGE in SQL Server Books Online.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in
message
>news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
>Is there a way to see the size in mb of each table in a
db?
>I am trying to troubleshoot a problem I am seeing. I
have 2 dbs with the
>same tables and very similar data in the tables, yet when
I right click on
>the db in SQL enterprise manager and select properties
the total db mb on 1
>is 45 times bigger in mb than the 2nd.
>Thx,
>Scott Buerkley
>
>.
>|||Most probably incorrect info in sysindexes. Did you try DBCC UPDATEUSAGE?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:bfce01c3ecd2$672940f0$a501280a@.phx.gbl...
> Hi Narayana,
> I have used your 'sp_show_huge_tables' Sp and got
>
> Row count Total space used (MB)
> 2026 -.09
> 1548 -2.07
> on two of my tables. How is this possible '
> Thanks.
>
> >--Original Message--
> >See if this helps:
> >http://vyaskn.tripod.com/sp_show_biggest_tables.htm
> >
> >Also see DBCC UPDATEUSAGE in SQL Server Books Online.
> >--
> >HTH,
> >Vyas, MVP (SQL Server)
> >http://vyaskn.tripod.com/
> >Is .NET important for a database professional?
> >http://vyaskn.tripod.com/poll.htm
> >
> >
> >"Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in
> message
> >news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> >Is there a way to see the size in mb of each table in a
> db?
> >
> >I am trying to troubleshoot a problem I am seeing. I
> have 2 dbs with the
> >same tables and very similar data in the tables, yet when
> I right click on
> >the db in SQL enterprise manager and select properties
> the total db mb on 1
> >is 45 times bigger in mb than the 2nd.
> >
> >Thx,
> >Scott Buerkley
> >
> >
> >
> >.
> >|||Thank You!!
This worked.
Thx,
Scott Buerkley
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OvSsK4M7DHA.1636@.TK2MSFTNGP12.phx.gbl...
> See if this helps:
> http://vyaskn.tripod.com/sp_show_biggest_tables.htm
> Also see DBCC UPDATEUSAGE in SQL Server Books Online.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
> news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> Is there a way to see the size in mb of each table in a db?
> I am trying to troubleshoot a problem I am seeing. I have 2 dbs with the
> same tables and very similar data in the tables, yet when I right click on
> the db in SQL enterprise manager and select properties the total db mb on
1
> is 45 times bigger in mb than the 2nd.
> Thx,
> Scott Buerkley
>
>|||I got a listing of my table sizes and as it turns out none were that large.
I keep investigating and found that the Transaction log file is the file
that is huge. Does anyone have any suggestions on how to fix this? I do
not need to roll back any data. It is all test data so far.
Thx,
Scott Buerkley
"Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> Is there a way to see the size in mb of each table in a db?
> I am trying to troubleshoot a problem I am seeing. I have 2 dbs with the
> same tables and very similar data in the tables, yet when I right click on
> the db in SQL enterprise manager and select properties the total db mb on
1
> is 45 times bigger in mb than the 2nd.
> Thx,
> Scott Buerkley
>|||Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
news:uFWhAaN7DHA.3804@.tk2msftngp13.phx.gbl...
> I got a listing of my table sizes and as it turns out none were that
large.
> I keep investigating and found that the Transaction log file is the file
> that is huge. Does anyone have any suggestions on how to fix this? I do
> not need to roll back any data. It is all test data so far.
> Thx,
> Scott Buerkley
> "Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
> news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > Is there a way to see the size in mb of each table in a db?
> >
> > I am trying to troubleshoot a problem I am seeing. I have 2 dbs with
the
> > same tables and very similar data in the tables, yet when I right click
on
> > the db in SQL enterprise manager and select properties the total db mb
on
> 1
> > is 45 times bigger in mb than the 2nd.
> >
> > Thx,
> > Scott Buerkley
> >
> >
>|||This helped.
Thank You!!!
Scott Buerkley
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23wY299N7DHA.1460@.tk2msftngp13.phx.gbl...
> Check out below KB articles:
> INF: How to Shrink the SQL Server 7.0 Transaction Log
> http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
> INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
> http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
> Log File Grows too big
> http://www.support.microsoft.com/?id=317375
> Log file filling up
> http://www.support.microsoft.com/?id=110139
> Considerations for Autogrow and AutoShrink
> http://www.support.microsoft.com/?id=315512
> http://www.mssqlserver.com/faq/logs-shrinklog.asp
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
> news:uFWhAaN7DHA.3804@.tk2msftngp13.phx.gbl...
> > I got a listing of my table sizes and as it turns out none were that
> large.
> > I keep investigating and found that the Transaction log file is the file
> > that is huge. Does anyone have any suggestions on how to fix this? I
do
> > not need to roll back any data. It is all test data so far.
> >
> > Thx,
> > Scott Buerkley
> >
> > "Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
> > news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > > Is there a way to see the size in mb of each table in a db?
> > >
> > > I am trying to troubleshoot a problem I am seeing. I have 2 dbs with
> the
> > > same tables and very similar data in the tables, yet when I right
click
> on
> > > the db in SQL enterprise manager and select properties the total db mb
> on
> > 1
> > > is 45 times bigger in mb than the 2nd.
> > >
> > > Thx,
> > > Scott Buerkley
> > >
> > >
> >
> >
>

List databases and size of an instance or sql server

Hi all
I looking the way to list the all the databases in a server and get other
properites such the size of each one.
The list of the databases of are in the master databaes, select name from
sysdatabases, but the other properties are inside on each databes (size,
last backup...) How i can use the select like a variable to connect to the
rest of the databases in the server o instance?
Best Regards
FernandoHi,
Execute the below system procedure from query analyzer:-
sp_helpdb
Thanks
Hari
SQL Server mvp
"Nano" <Nano@.discussions.microsoft.com> wrote in message
news:86D15A09-972A-4D12-8893-D3FE79C4FCFC@.microsoft.com...
> Hi all
> I looking the way to list the all the databases in a server and get other
> properites such the size of each one.
> The list of the databases of are in the master databaes, select name from
> sysdatabases, but the other properties are inside on each databes (size,
> last backup...) How i can use the select like a variable to connect to the
> rest of the databases in the server o instance?
> Best Regards
> Fernando