Size and Name of all Databases

Question

Post reply

Size and Name of all Databases

sps-554505

Default port

Points: 1463
More actions
April 15, 2008 at 12:31 am

#128612

Hi,
Is there any system tables which i can find both (all the database details and Size of the database details)
Smm

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

jezemine SSCrazy Eights Points: 8059 More actions · Answer 1

have a look at sys.databases and sys.database_files

---------------------------------------
elsasoft.org

sps-554505 Default port Points: 1463 More actions · Answer 2

i have tried with both but select * from sys.databases

it is not giving details about size,

and the other one not giving all the db size details,we need to run in each DB

jezemine SSCrazy Eights Points: 8059 More actions · Answer 3

you could do something like this:

declare @sql nvarchar(4000)

declare @name sysname

declare @t table (dbname sysname, filename sysname)

-- loop over all databases using a cursor/while loop, which I am too lazy to do here...

set @name=N'AdventureWorks' -- set this in your loop, don't hard code as here

set @sql=N'select ''' + @name +''', physical_name from ' + quotename(@name) + '.sys.database_files'

insert @t exec(@sql)

select * from @t

---------------------------------------
elsasoft.org

Phil Knott-411329 Valued Member Points: 74 More actions · Answer 4

Is this what your after?

USE master

GO

SELECT size, name, filename

FROM sysaltfiles

GO

jezemine SSCrazy Eights Points: 8059 More actions · Answer 5

nice. wasn't aware of that one. 🙂

---------------------------------------
elsasoft.org

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

If you like "pretty" - make sure to download the Performance Dashboard reports from MS' download site. Has a very nice databases summary report (sizes and more)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Manish_ Ten Centuries Points: 1070 More actions · Answer 7

sp_spaceused

This system stored procedure will return the size statistics for the current database context in which it is running. It is very useful for returning ad hoc information regarding database or table sizes within the database; however, it is not very friendly for reporting purposes. It is possible to capture the information for each database through a script, but it would require the use of a user-defined cursor.

- Manish

gmby SSCarpal Tunnel Points: 4511 More actions · Answer 8

I would recommend using the SQL2005 system view sys.master_files rather than sysaltfiles - if you do not need backward compatibility with 2000.

jg

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 9

The script on the link below produces a detailed analysis of the file size information for all databases on a SQL Server.

Get Server Database File Information

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

Totals for the following are produced:

File Details

Total by Database and File

Total by Database and Filegroup

Total by Database and Filegroup Type

Total by Disk, Database, and Filepath

Total by Disk and Database

Total by Database

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 10

Phil Knott (4/16/2008)
Is this what your after?
USE master
GO
SELECT size, name, filename
FROM sysaltfiles
GO

Thank you for that (I like clean and simple!)

When I run it, it fails to show the name of my primary database, and instead lists the servername (_dat and _log). Is this another situation (like configuration/schema changes history) where an original name is preserved instead of the current name?

Chandrachurh Ghosh Hall of Fame Points: 3001 More actions · Answer 11

Use the following script -

USE master

GO

SELECT B.[Name] AS [Database Name (Logical)],

A.[Name] AS [Physical Name],

A.[filename] AS [File Path],

A. AS [File Size]

FROM sysaltfiles A

INNER JOIN sys.databases B

ON A.DBID=B.Database_ID

--WHERE B.[Name]='PRACTICE'

GO

Note: You can use a specific database name to retrive relevant information

Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 12

Better names, thanks chandrachurhghosh.

(Unrelated dumb question, why do you and Phil have a "GO" at the end of your queries?)

jezemine SSCrazy Eights Points: 8059 More actions · Answer 13

it makes it go faster. 😀

---------------------------------------
elsasoft.org

shashi kant SSCommitted Points: 1659 More actions · Answer 14

Try....

Sp_helpdb

and

sp_msforeachdb 'sp_helpdb [?]'

Regards

shashi kant chauhan 🙂