Find all Databases size in SQL Server


CREATE

PROCEDURE PROD_DB_SIZE

AS

 

BEGIN

set

 

 

nocount

on

declare

 

 

@name

sysname

declare

 

 

@SQL nvarchar(600

)

 

/* Use temporary table to sum up database size w/o using group by */

create

table #databases

(

 

DATABASE_NAME

sysnameNOTNULL,

size

intNOTNULL)

declare

c1 cursorfor

select

name frommaster.dbo.

sysdatabases

 

where

has_dbaccess(name)= 1 and name notin(‘master’,‘model’,‘msdb’,‘Northwind’,‘pubs’,‘tempdb’)

— Only look at databases to which we have access

 

open

c1

fetch

c1 into @name

while

@@fetch_status>= 0

begin

select

@SQL =

‘insert into #databases

select N”’

 

 

+ @name +

”’, sum(size) from ‘

 

+

QuoteName(@name)+

‘.dbo.sysfiles’

 

/* Insert row for each database */

execute

(@SQL

)

 

fetch

c1 into @name

end

deallocate

c1

select

 

[DATABASE_NAME]

,

[DATABASE_SIZE in GB]

= size*8/1024 /* Convert from 8192 byte pages to K */

from

#databases

— order by DATABASE_SIZE

order

by 2

desc

END

 

 

— EXEC PROD_DB_SIZE

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: