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
