USE master;
GO
IF OBJECT_ID(
'dbo.sp_SDS'
,
'P'
)
IS
NOT
NULL
DROP
PROCEDURE
dbo.sp_SDS;
GO
CREATE
PROCEDURE
dbo.sp_SDS
@TargetDatabase nvarchar(128) =
NULL
,
@
Level
varchar
(10) =
'Database'
,
@UpdateUsage
bit
= 0,
@Unit
char
(2) =
'MB'
AS
/**************************************************************************************************
**
** author: Richard Ding
**
date
: 4/8/2008
** usage: list db
size
AND
path w/o SUMmary
** test code: sp_SDS
** sp_SDS
'maAster'
** sp_SDS
NULL
,
NULL
, 0
** sp_SDS
NULL
,
'file'
, 1,
'GB'
** sp_SDS
'Test_snapshot'
,
'Database'
, 1
** sp_SDS
'Test'
,
'File'
, 0,
'kb'
** sp_SDS
'pfaids'
,
'Database'
, 0,
'gb'
** sp_SDS
'tempdb'
,
NULL
, 1,
'kb'
**
**************************************************************************************************/
SET
NOCOUNT
ON
;
IF @TargetDatabase
IS
NOT
NULL
AND
DB_ID(@TargetDatabase)
IS
NULL
BEGIN
RAISERROR(15010, -1, -1, @TargetDatabase);
RETURN
(-1)
END
IF OBJECT_ID(
'tempdb.dbo.##Tbl_CombinedInfo'
,
'U'
)
IS
NOT
NULL
DROP
TABLE
dbo.##Tbl_CombinedInfo;
IF OBJECT_ID(
'tempdb.dbo.##Tbl_DbFileStats'
,
'U'
)
IS
NOT
NULL
DROP
TABLE
dbo.##Tbl_DbFileStats;
IF OBJECT_ID(
'tempdb.dbo.##Tbl_ValidDbs'
,
'U'
)
IS
NOT
NULL
DROP
TABLE
dbo.##Tbl_ValidDbs;
IF OBJECT_ID(
'tempdb.dbo.##Tbl_Logs'
,
'U'
)
IS
NOT
NULL
DROP
TABLE
dbo.##Tbl_Logs;
CREATE
TABLE
dbo.##Tbl_CombinedInfo (
DatabaseName sysname
NULL
,
[type]
VARCHAR
(10)
NULL
,
LogicalName sysname
NULL
,
T
dec
(10, 2)
NULL
,
U
dec
(10, 2)
NULL
,
[U(%)]
dec
(5, 2)
NULL
,
F
dec
(10, 2)
NULL
,
[F(%)]
dec
(5, 2)
NULL
,
PhysicalName nvarchar(255)
NULL
);
CREATE
TABLE
dbo.##Tbl_DbFileStats (
Id
int
identity,
DatabaseName sysname
NULL
,
FileId
int
NULL
,
FileGroup
int
NULL
,
TotalExtents
bigint
NULL
,
UsedExtents
bigint
NULL
,
Name
sysname
NULL
,
FileName nvarchar(255)
NULL
);
CREATE
TABLE
dbo.##Tbl_ValidDbs (
Id
int
identity,
Dbname sysname
NULL
);
CREATE
TABLE
dbo.##Tbl_Logs (
DatabaseName sysname
NULL
,
LogSize
dec
(10, 2)
NULL
,
LogSpaceUsedPercent
dec
(5, 2)
NULL
,
Status
int
NULL
);
DECLARE
@Ver
varchar
(20),
@DatabaseName sysname,
@Ident_last
int
,
@String
varchar
(2000),
@BaseString
varchar
(2000);
SELECT
@DatabaseName =
''
,
@Ident_last = 0,
@String =
''
,
@Ver =
CASE
WHEN
@@VERSION
LIKE
'%9.0%'
THEN
'SQL 2005'
WHEN
@@VERSION
LIKE
'%8.0%'
THEN
'SQL 2000'
WHEN
@@VERSION
LIKE
'%10.0%'
THEN
'SQL 2008'
WHEN
@@VERSION
LIKE
'%10.5%'
THEN
'SQL 2008 R2'
WHEN
@@VERSION
LIKE
'%11.0%'
THEN
'SQL 2012'
END
;
SELECT
@BaseString =
' SELECT DB_NAME(), '
+
CASE
WHEN
@Ver =
'SQL 2000'
THEN
'CASE WHEN status & 0x40 = 0x40 THEN '
'Log'
' ELSE '
'Data'
' END'
ELSE
' CASE type WHEN 0 THEN '
'Data'
' WHEN 1 THEN '
'Log'
' WHEN 4 THEN '
'Full-text'
' ELSE '
'reserved'
' END'
END
+
', name, '
+
CASE
WHEN
@Ver =
'SQL 2000'
THEN
'filename'
ELSE
'physical_name'
END
+
', size*8.0/1024.0 FROM '
+
CASE
WHEN
@Ver =
'SQL 2000'
THEN
'sysfiles'
ELSE
'sys.database_files'
END
+
' WHERE '
+
CASE
WHEN
@Ver =
'SQL 2000'
THEN
' HAS_DBACCESS(DB_NAME()) = 1'
ELSE
'state_desc = '
'ONLINE'
''
END
+
''
;
SELECT
@String =
'INSERT INTO dbo.##Tbl_ValidDbs SELECT name FROM '
+
CASE
WHEN
@Ver =
'SQL 2000'
THEN
'master.dbo.sysdatabases'
WHEN
@Ver
IN
(
'SQL 2005'
,
'SQL 2008'
,
'SQL 2008 R2'
,
'SQL 2012'
)
THEN
'master.sys.databases'
END
+
' WHERE HAS_DBACCESS(name) = 1 ORDER BY name ASC'
;
EXEC
(@String);
INSERT
INTO
dbo.##Tbl_Logs
EXEC
(
'DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS'
);
IF @TargetDatabase
IS
NOT
NULL
BEGIN
SELECT
@DatabaseName = @TargetDatabase;
IF @UpdateUsage <> 0
AND
DATABASEPROPERTYEX (@DatabaseName,
'Status'
) =
'ONLINE'
AND
DATABASEPROPERTYEX (@DatabaseName,
'Updateability'
) <>
'READ_ONLY'
BEGIN
SELECT
@String =
'USE ['
+ @DatabaseName +
'] DBCC UPDATEUSAGE (0)'
;
PRINT
'*** '
+ @String +
' *** '
;
EXEC
(@String);
PRINT
''
;
END
SELECT
@String =
'INSERT INTO dbo.##Tbl_CombinedInfo (DatabaseName, type, LogicalName, PhysicalName, T) '
+ @BaseString;
INSERT
INTO
dbo.##Tbl_DbFileStats (FileId, FileGroup, TotalExtents, UsedExtents,
Name
, FileName)
EXEC
(
'USE ['
+ @DatabaseName +
'] DBCC SHOWFILESTATS WITH NO_INFOMSGS'
);
EXEC
(
'USE ['
+ @DatabaseName +
'] '
+ @String);
UPDATE
dbo.##Tbl_DbFileStats
SET
DatabaseName = @DatabaseName;
END
ELSE
BEGIN
WHILE 1 = 1
BEGIN
SELECT
TOP
1 @DatabaseName = Dbname
FROM
dbo.##Tbl_ValidDbs
WHERE
Dbname > @DatabaseName
ORDER
BY
Dbname
ASC
;
IF @@ROWCOUNT = 0
BREAK;
IF @UpdateUsage <> 0
AND
DATABASEPROPERTYEX (@DatabaseName,
'Status'
) =
'ONLINE'
AND
DATABASEPROPERTYEX (@DatabaseName,
'Updateability'
) <>
'READ_ONLY'
BEGIN
SELECT
@String =
'DBCC UPDATEUSAGE ('
''
+ @DatabaseName +
''
') '
;
PRINT
'*** '
+ @String +
'*** '
;
EXEC
(@String);
PRINT
''
;
END
SELECT
@Ident_last =
ISNULL
(
MAX
(Id), 0)
FROM
dbo.##Tbl_DbFileStats;
SELECT
@String =
'INSERT INTO dbo.##Tbl_CombinedInfo (DatabaseName, type, LogicalName, PhysicalName, T) '
+ @BaseString;
EXEC
(
'USE ['
+ @DatabaseName +
'] '
+ @String);
INSERT
INTO
dbo.##Tbl_DbFileStats (FileId, FileGroup, TotalExtents, UsedExtents,
Name
, FileName)
EXEC
(
'USE ['
+ @DatabaseName +
'] DBCC SHOWFILESTATS WITH NO_INFOMSGS'
);
UPDATE
dbo.##Tbl_DbFileStats
SET
DatabaseName = @DatabaseName
WHERE
Id
BETWEEN
@Ident_last + 1
AND
@@IDENTITY;
END
END
UPDATE
dbo.##Tbl_CombinedInfo
SET
U = s.UsedExtents*8*8/1024.0
FROM
dbo.##Tbl_CombinedInfo t
JOIN
dbo.##Tbl_DbFileStats s
ON
t.LogicalName = s.
Name
AND
s.DatabaseName = t.DatabaseName;
UPDATE
dbo.##Tbl_CombinedInfo
SET
[U(%)] = LogSpaceUsedPercent,
U = T * LogSpaceUsedPercent/100.0
FROM
dbo.##Tbl_CombinedInfo t
JOIN
dbo.##Tbl_Logs l
ON
l.DatabaseName = t.DatabaseName
WHERE
t.type =
'Log'
;
UPDATE
dbo.##Tbl_CombinedInfo
SET
F = T - U, [U(%)] = U*100.0/T;
UPDATE
dbo.##Tbl_CombinedInfo
SET
[F(%)] = F*100.0/T;
IF
UPPER
(
ISNULL
(@
Level
,
'DATABASE'
)) =
'FILE'
BEGIN
IF @Unit =
'KB'
UPDATE
dbo.##Tbl_CombinedInfo
SET
T = T * 1024, U = U * 1024, F = F * 1024;
IF @Unit =
'GB'
UPDATE
dbo.##Tbl_CombinedInfo
SET
T = T / 1024, U = U / 1024, F = F / 1024;
SELECT
DatabaseName
AS
'Database'
,
type
AS
'Type'
,
LogicalName,
T
AS
'Total'
,
U
AS
'Used'
,
[U(%)]
AS
'Used (%)'
,
F
AS
'Free'
,
[F(%)]
AS
'Free (%)'
,
PhysicalName
FROM
dbo.##Tbl_CombinedInfo
WHERE
DatabaseName
LIKE
ISNULL
(@TargetDatabase,
'%'
)
ORDER
BY
DatabaseName
ASC
, type
ASC
;
SELECT
CASE
WHEN
@Unit =
'GB'
THEN
'GB'
WHEN
@Unit =
'KB'
THEN
'KB'
ELSE
'MB'
END
AS
'SUM'
,
SUM
(T)
AS
'TOTAL'
,
SUM
(U)
AS
'USED'
,
SUM
(F)
AS
'FREE'
FROM
dbo.##Tbl_CombinedInfo;
END
IF
UPPER
(
ISNULL
(@
Level
,
'DATABASE'
)) =
'DATABASE'
BEGIN
DECLARE
@Tbl_Final
TABLE
(
DatabaseName sysname
NULL
,
TOTAL
dec
(10, 2),
[=]
char
(1),
used
dec
(10, 2),
[used (%)]
dec
(5, 2),
[+]
char
(1),
free
dec
(10, 2),
[
free
(%)]
dec
(5, 2),
[==]
char
(2),
Data
dec
(10, 2),
Data_Used
dec
(10, 2),
[Data_Used (%)]
dec
(5, 2),
Data_Free
dec
(10, 2),
[Data_Free (%)]
dec
(5, 2),
[++]
char
(2),
Log
dec
(10, 2),
Log_Used
dec
(10, 2),
[Log_Used (%)]
dec
(5, 2),
Log_Free
dec
(10, 2),
[Log_Free (%)]
dec
(5, 2) );
INSERT
INTO
@Tbl_Final
SELECT
x.DatabaseName,
x.Data + y.Log
AS
'TOTAL'
,
'='
AS
'='
,
x.Data_Used + y.Log_Used
AS
'U'
,
(x.Data_Used + y.Log_Used)*100.0 / (x.Data + y.Log)
AS
'U(%)'
,
'+'
AS
'+'
,
x.Data_Free + y.Log_Free
AS
'F'
,
(x.Data_Free + y.Log_Free)*100.0 / (x.Data + y.Log)
AS
'F(%)'
,
'=='
AS
'=='
,
x.Data,
x.Data_Used,
x.Data_Used*100/x.Data
AS
'D_U(%)'
,
x.Data_Free,
x.Data_Free*100/x.Data
AS
'D_F(%)'
,
'++'
AS
'++'
,
y.Log,
y.Log_Used,
y.Log_Used*100/y.Log
AS
'L_U(%)'
,
y.Log_Free,
y.Log_Free*100/y.Log
AS
'L_F(%)'
FROM
(
SELECT
d.DatabaseName,
SUM
(d.T)
AS
'Data'
,
SUM
(d.U)
AS
'Data_Used'
,
SUM
(d.F)
AS
'Data_Free'
FROM
dbo.##Tbl_CombinedInfo d
WHERE
d.type =
'Data'
GROUP
BY
d.DatabaseName )
AS
x
JOIN
(
SELECT
l.DatabaseName,
SUM
(l.T)
AS
'Log'
,
SUM
(l.U)
AS
'Log_Used'
,
SUM
(l.F)
AS
'Log_Free'
FROM
dbo.##Tbl_CombinedInfo l
WHERE
l.type =
'Log'
GROUP
BY
l.DatabaseName )
AS
y
ON
x.DatabaseName = y.DatabaseName;
IF @Unit =
'KB'
UPDATE
@Tbl_Final
SET
TOTAL = TOTAL * 1024,
used = used * 1024,
free
=
free
* 1024,
Data = Data * 1024,
Data_Used = Data_Used * 1024,
Data_Free = Data_Free * 1024,
Log = Log * 1024,
Log_Used = Log_Used * 1024,
Log_Free = Log_Free * 1024;
IF @Unit =
'GB'
UPDATE
@Tbl_Final
SET
TOTAL = TOTAL / 1024,
used = used / 1024,
free
=
free
/ 1024,
Data = Data / 1024,
Data_Used = Data_Used / 1024,
Data_Free = Data_Free / 1024,
Log = Log / 1024,
Log_Used = Log_Used / 1024,
Log_Free = Log_Free / 1024;
DECLARE
@GrantTotal
dec
(11, 2);
SELECT
@GrantTotal =
SUM
(TOTAL)
FROM
@Tbl_Final;
SELECT
CONVERT
(
dec
(10, 2), TOTAL*100.0/@GrantTotal)
AS
'WEIGHT (%)'
,
DatabaseName
AS
'DATABASE'
,
CONVERT
(
VARCHAR
(12), used) +
' ('
+
CONVERT
(
VARCHAR
(12), [used (%)]) +
' %)'
AS
'USED (%)'
,
[+],
CONVERT
(
VARCHAR
(12),
free
) +
' ('
+
CONVERT
(
VARCHAR
(12), [
free
(%)]) +
' %)'
AS
'FREE (%)'
,
[=],
TOTAL,
[=],
CONVERT
(
VARCHAR
(12), Data) +
' ('
+
CONVERT
(
VARCHAR
(12), Data_Used) +
', '
+
CONVERT
(
VARCHAR
(12), [Data_Used (%)]) +
'%)'
AS
'DATA (used, %)'
,
[+],
CONVERT
(
VARCHAR
(12), Log) +
' ('
+
CONVERT
(
VARCHAR
(12), Log_Used) +
', '
+
CONVERT
(
VARCHAR
(12), [Log_Used (%)]) +
'%)'
AS
'LOG (used, %)'
FROM
@Tbl_Final
WHERE
DatabaseName
LIKE
ISNULL
(@TargetDatabase,
'%'
)
ORDER
BY
DatabaseName
ASC
;
IF @TargetDatabase
IS
NULL
SELECT
CASE
WHEN
@Unit =
'GB'
THEN
'GB'
WHEN
@Unit =
'KB'
THEN
'KB'
ELSE
'MB'
END
AS
'SUM'
,
SUM
(used)
AS
'USED'
,
SUM
(
free
)
AS
'FREE'
,
SUM
(TOTAL)
AS
'TOTAL'
,
SUM
(Data)
AS
'DATA'
,
SUM
(Log)
AS
'LOG'
FROM
@Tbl_Final;
END
RETURN
(0)
GO