sql server 获取服务器中数据库的大小

create   table  # Temp (DatabaseName  varchar ( 200 ), big  float , type  int )
declare   @sql   varchar ( 1000 ),  @i   int ,  @Name   varchar ( 200 )

     set   @sql = ' INSERT INTO #Temp SELECT name, size*8.0/1024,type FROM  '
     set   @sql   =   @sql   + ' [?].sys.database_files '

     EXECUTE  sp_msforeachdb  @sql

select   ' Data file '   as  FType, ( select   sum (big)  from  # Temp   where  type = 0 )  as  fsize
union   all
select   ' Data log ' ,( select   sum (big)  from  # Temp   where  type = 1 )
drop   table  # Temp

     exec  sp_addlinkedserver
             @server   = ' Server ' ,
             @srvproduct   =   '' ,
             @provider   =  N ' SQLOLEDB ' ,
             @datasrc   =  N ' 192.168.0.1 ' ,
             @catalog   =  N ' databasename '

     exec  sp_addlinkedsrvlogin
             @rmtsrvname   =   ' Server ' ,
             @useself = ' False ' ,
             @rmtuser   = ' user id ' ,
             @rmtpassword   = ' user password '

         select   [ Server ] . [ databasename ] .sys.database_files

     exec    sp_dropserver    ' Server ' , ' droplogins '

 

转载于:https://www.cnblogs.com/lfzwenzhu/archive/2011/05/19/2050806.html