听雪堂 » Blog Archive » SQL Server脚本--列出所有数据库文件,父子节点关系约束

最近查了一些别人写的数据库脚本.印象比较深的是这么两个:

1. 列出所有数据库文件.

这是因为要做几个数据库的迁移, 迁移之前需要对所有的物理文件以及逻辑文件做一个整理. Windows 的SQL Server本身提供了一个系统存储过程 - sp_spaceused, 不过这个返回的结果集比较有限.常用的方法还是去系统表sysfiles中找. 嗯, 按照往常的做法, 一个循环, 把全部的数据库列一下, 需要的东西就有了. 有意思的是, 今天在MSSQLTips网站上看到了这段:

DECLARE @DBInfo TABLE
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime) 

DECLARE @command VARCHAR(5000) 

SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +
       'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
GETDATE() as PollDate FROM dbo.sysfiles'
INSERT INTO @DBInfo
   (ServerName,
   DatabaseName,
   FileSizeMB,
   LogicalFileName,
   PhysicalFileName,
   Status,
   Updateability,
   RecoveryMode,
   FreeSpaceMB,
   FreeSpacePct,
   PollDate)
EXEC sp_MSForEachDB @command 

SELECT
   ServerName,
   DatabaseName,
   FileSizeMB,
   LogicalFileName,
   PhysicalFileName,
   Status,
   Updateability,
   RecoveryMode,
   FreeSpaceMB,
   FreeSpacePct,
   PollDate
FROM @DBInfo
ORDER BY
   ServerName,
   DatabaseName

其实唯一吸引我的是EXEC sp_MSForEachDB这一句, 原来还可以这样用... 害我一直自己傻了吧唧地写循环. 实际上, 除了sp_MSForEachDB, 还有sp_MSForEachTable的用法.

另外, 上面的那段脚本在SQL Server 2000里面是执行不通的, 在2000的版本中, 不允许直接用exec的结果集做为插入的数据源集. 我稍微改了一下. 把插入数据的动作直接放到exec sp_MSForEachDB 里. 结果集用临时表做暂存,这样就通用了:

create TABLE #DBInfo
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime) 

DECLARE @command VARCHAR(5000) 

SELECT @command = 'INSERT INTO #DBInfo
   (ServerName,
   DatabaseName,
   FileSizeMB,
   LogicalFileName,
   PhysicalFileName,
   Status,
   Updateability,
   RecoveryMode,
   FreeSpaceMB,
   FreeSpacePct,
   PollDate)  SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +
       'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
GETDATE() as PollDate FROM dbo.sysfiles' 

EXEC sp_MSForEachDB @command 

SELECT
   ServerName,
   DatabaseName,
   FileSizeMB,
   LogicalFileName,
   PhysicalFileName,
   Status,
   Updateability,
   RecoveryMode,
   FreeSpaceMB,
   FreeSpacePct,
   PollDate
FROM #DBInfo
ORDER BY
   ServerName,
   DatabaseName

2. 用路径表述的父子关系在插入数据库时做约束

这个要求看起来有点变态, 实际上, 很多情况下, 我的感觉是, 这种确保数据关系安全的逻辑判断应该放在应用服务器或者中间层服务器上, 而不应当占用数据库的资源.

嗯, 这个命题和解法是在sqlblogcasts上看到. 脚本是这样的:

create table Org (
  NodePath    varchar(200) not null primary key
, ParentPath  as cast(case when charindex('\',NodePath) = len(NodePath) then null
                           else left(NodePath,len(NodePath)-charindex('\',reverse(NodePath),2)+1)
                end as varchar(200)) persisted)

go

alter table Org add constraint fk_Org_ParentPath foreign key (ParentPath) references Org(NodePath)

下面这些插入操作中,最后一笔由于不符合约束条件而失败.

insert into Org values ('10\')
insert into Org values ('10\9\')
insert into Org values ('11\')
insert into Org values ('11\9\9\')

对ParentPath的判断中, reverse方法我用的不多. 这让我想起了前阵子跟Cyndi讨论的手机号码来电和拨号时的显示匹配问题(因为很多时候我们会在号码前加前缀,+86,025,17951...). 也许反转过来的匹配就比较好做了.