i want to get database name,database owner and database size in a single query(insted of sp_helpdb)

• jayasree.lingala

  SSC Journeyman

  Points: 86

  More actions

  June 25, 2009 at 5:22 am

  #384846

  Hi all,

  Thanks for quick reply...but in sysdatabases there is no coloumn for size of the database.

  Advanced thanks

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  June 25, 2009 at 5:25 am

  #1015212

  Check sysdatabases

• hi_abhay78

  SSCertifiable

  Points: 6265

  More actions

  June 25, 2009 at 5:56 am

  #1015229

  1)

  create table test

  ([name] sysname,db_size nvarchar(13),

  [owner] sysname null,[dbid] smallint,

  created nvarchar(11),[status] nvarchar(600),

  compatibility tinyint)

  2)

  insert into test exec sp_helpdb

  3)

  select name ,db_size,owner from test

  HTH

  Abhay Chaudhary
  Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

• hi_abhay78

  SSCertifiable

  Points: 6265

  More actions

  June 25, 2009 at 5:57 am

  #1015231

  sorry , you can also create a temp table #test

  Regards

  Abhay Chaudhary
  Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  June 25, 2009 at 6:04 am

  #1015236

  @jayasree.lingala

  Please use the "Reply" button to reply to solutions supplied by people here. If you edit your previous post we (I) don't get a mail notification that new information are available.

  hi_abhay78's answer should cover your required information.

  Btw: Your initial post was quiet "short". Please provide a more information in future threads, not just a subject

• george sibbald

  SSC Guru

  Points: 104210

  More actions

  June 25, 2009 at 7:04 am

  #1015272

  --SQL 2000

  returns dbname, total size of all data files in MB, size of latest backup in MB,recovery mode and owner for all databases that have been backed up.

  SELECT

  A.NAME,

  STR(CAST(SUM(SIZE) AS NUMERIC)*8192/1048576) AS 'DATA FILE(S) SIZE IN MB',

  FLOOR(B.BACKUP_SIZE/1048576) AS 'BACKUP SIZE IN MB',

  convert(sysname,DatabasePropertyEx(a.name,'Recovery')) as 'Recovery Mode'

  ,suser_sname(a.sid) as 'dbowner'

  FROM (SELECT

  MAX(BACKUP_START_DATE) AS BACKUP_START_DATE,

  DATABASE_NAME

  FROM MSDB..BACKUPSET

  WHERE TYPE = 'D'

  GROUP BY DATABASE_NAME) X

  INNER JOIN MSDB..BACKUPSET B ON X.DATABASE_NAME = B.DATABASE_NAME

  AND B.BACKUP_START_DATE = X.BACKUP_START_DATE

  RIGHT OUTER JOIN MASTER..SYSDATABASES A ON A.NAME = B.DATABASE_NAME

  INNER JOIN MASTER..SYSALTFILES C ON A.DBID = C.DBID

  AND GROUPID != 0

  WHERE A.DBID > 4

  GROUP BY

  A.NAME,

  B.BACKUP_SIZE,

  a.sid

  ORDER BY A.NAME

  ---------------------------------------------------------------------