检查数据库名称与数据库逻辑名、物理文件名称是否一致，并生成修复脚本
检查数据库名称与数据库逻辑名、物理文件名称是否一致，并生成修复脚本：
（目前只考虑一个数据库有mdf,ldf两个文件，要是存在多个文件组，就不能使用下面的方法，因为对于多个文件组的数据库，用户可以有自己的文件命名规则。当然可以根据实际的命名规则，修改下面语句来实现判断各名称是否一致、合理）
测试下面的语句时，请在测试机器上执行，本代码不保证所有兼容，和安全性。
Set Nocount On
use master
If Object_id(N'tempdb..#') Is Not null
Drop Table #
Create Table #(
[database] nvarchar(128),
[name] varchar(128),
[filename] nvarchar(1024),
[Path] nvarchar(1024),
[filegroup] nvarchar(50),
Flag int
)
Declare @Sql nvarchar(4000)
Declare @i int,
@MaxID int
Select @i=1,@MaxID=Max(dbid) From master.sys.sysdatabases
While @i<=@MaxID
Begin
Set @Sql=''
Select @Sql=@Sql+char(13)+char(10)+'Insert Into # ([database],[name],[filename],[filegroup])
Select '''+[name]+''',A.[name],A.[filename],B.[groupname] From '+Quotename([name])+'.sys.sysfiles A
Left Outer Join sys.sysfilegroups B On A.groupid=B.groupid
Where Replace([name],'''+[name]+''','''') Not In('''',''_data'',''_log'') Or
Replace(Right([filename],Charindex(''\'',Reverse([filename]))-1),'''+[name]+''','''') Not In (''.mdf'',''_data.mdf'',''_log.ldf'')'
From master.sys.sysdatabases
Where dbid=@i And [name] Not In('master','model','msdb','tempdb')
Set @i=@i+1
exec (@Sql)
End
Update #
Set @i=Charindex('\',Reverse([filename]))
,[Path]=Stuff([filename],len([filename])-@i+2,@i,'')
,Flag=Case when Replace([name],[database],'') Not In('','_data','_log') Then 1 Else 0 End +
Case When Replace(Right([filename],@i-1),[database],'') Not In('.mdf','_data.mdf','_log.ldf') Then 2 Else 0 End
Set @Sql=''
If Exists(Select 1 From #)
Begin
Set @Sql='Use master '
Select @Sql=@Sql+char(13)+char(10)+'Alter database '+quotename([database])+ ' Modify File (Name='+[name]+
Case When flag<>2 And [filegroup]='PRIMARY' Then ',Newname='+[database]+'_data'
When flag<>2 And [filegroup]Is null Then ',Newname='+[database]+'_log'
Else '' End +
Case When flag>1 And [filegroup]='PRIMARY' Then ',Filename='''+[path]+[database]+'_data.mdf'''
When flag>1 And [filegroup] Is null Then ',Filename='''+[path]+[database]+'_log.ldf'''
Else '' End+')'+Char(13)+char(10)+
Case When flag>1 And [filegroup]='PRIMARY' Then 'Alter database '+quotename([database])+' Set Offline'+Char(13)+char(10)+'Exec xp_cmdshell ''rename '+quotename([filename],'"')+' '+[database]+'_data.mdf'+''''+char(13)+char(10)+'Alter database '+quotename([database])+' Set Online'
When flag>1 And [filegroup] Is null Then 'Alter database '+quotename([database])+' Set Offline'+Char(13)+char(10)+'Exec xp_cmdshell ''rename '+quotename([filename],'"')+' '+[database]+'_log.ldf'+''''+char(13)+char(10)+'Alter database '+quotename([database])+' Set Online'
Else '' End
From #
End
If @Sql>''
Print @Sql --Exec (@Sql)
如随便建立一个测试数据库test, 把Test数据库名称改成MyTempDB
调用上面的SQL语句就会生成一下的修复脚本：
/*
Use master
Alter database [MyTempDB] Modify File (Name=Test_data,Newname=MyTempDB_data,Filename='D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyTempDB_data.mdf')
Alter database [MyTempDB] Set Offline
Exec xp_cmdshell 'rename "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Test_data.mdf" MyTempDB_data.mdf'
Alter database [MyTempDB] Set Online
Alter database [MyTempDB] Modify File (Name=Test_log,Newname=MyTempDB_log,Filename='D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyTempDB_log.ldf')
Alter database [MyTempDB] Set Offline
Exec xp_cmdshell 'rename "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Test_log.ldf" MyTempDB_log.ldf'
Alter database [MyTempDB] Set Online
*/
（目前只考虑一个数据库有mdf,ldf两个文件，要是存在多个文件组，就不能使用下面的方法，因为对于多个文件组的数据库，用户可以有自己的文件命名规则。当然可以根据实际的命名规则，修改下面语句来实现判断各名称是否一致、合理）
测试下面的语句时，请在测试机器上执行，本代码不保证所有兼容，和安全性。
Set Nocount On
use master
If Object_id(N'tempdb..#') Is Not null
Drop Table #
Create Table #(
[database] nvarchar(128),
[name] varchar(128),
[filename] nvarchar(1024),
[Path] nvarchar(1024),
[filegroup] nvarchar(50),
Flag int
)
Declare @Sql nvarchar(4000)
Declare @i int,
@MaxID int
Select @i=1,@MaxID=Max(dbid) From master.sys.sysdatabases
While @i<=@MaxID
Begin
Set @Sql=''
Select @Sql=@Sql+char(13)+char(10)+'Insert Into # ([database],[name],[filename],[filegroup])
Select '''+[name]+''',A.[name],A.[filename],B.[groupname] From '+Quotename([name])+'.sys.sysfiles A
Left Outer Join sys.sysfilegroups B On A.groupid=B.groupid
Where Replace([name],'''+[name]+''','''') Not In('''',''_data'',''_log'') Or
Replace(Right([filename],Charindex(''\'',Reverse([filename]))-1),'''+[name]+''','''') Not In (''.mdf'',''_data.mdf'',''_log.ldf'')'
From master.sys.sysdatabases
Where dbid=@i And [name] Not In('master','model','msdb','tempdb')
Set @i=@i+1
exec (@Sql)
End
Update #
Set @i=Charindex('\',Reverse([filename]))
,[Path]=Stuff([filename],len([filename])-@i+2,@i,'')
,Flag=Case when Replace([name],[database],'') Not In('','_data','_log') Then 1 Else 0 End +
Case When Replace(Right([filename],@i-1),[database],'') Not In('.mdf','_data.mdf','_log.ldf') Then 2 Else 0 End
Set @Sql=''
If Exists(Select 1 From #)
Begin
Set @Sql='Use master '
Select @Sql=@Sql+char(13)+char(10)+'Alter database '+quotename([database])+ ' Modify File (Name='+[name]+
Case When flag<>2 And [filegroup]='PRIMARY' Then ',Newname='+[database]+'_data'
When flag<>2 And [filegroup]Is null Then ',Newname='+[database]+'_log'
Else '' End +
Case When flag>1 And [filegroup]='PRIMARY' Then ',Filename='''+[path]+[database]+'_data.mdf'''
When flag>1 And [filegroup] Is null Then ',Filename='''+[path]+[database]+'_log.ldf'''
Else '' End+')'+Char(13)+char(10)+
Case When flag>1 And [filegroup]='PRIMARY' Then 'Alter database '+quotename([database])+' Set Offline'+Char(13)+char(10)+'Exec xp_cmdshell ''rename '+quotename([filename],'"')+' '+[database]+'_data.mdf'+''''+char(13)+char(10)+'Alter database '+quotename([database])+' Set Online'
When flag>1 And [filegroup] Is null Then 'Alter database '+quotename([database])+' Set Offline'+Char(13)+char(10)+'Exec xp_cmdshell ''rename '+quotename([filename],'"')+' '+[database]+'_log.ldf'+''''+char(13)+char(10)+'Alter database '+quotename([database])+' Set Online'
Else '' End
From #
End
If @Sql>''
Print @Sql --Exec (@Sql)
如随便建立一个测试数据库test, 把Test数据库名称改成MyTempDB
调用上面的SQL语句就会生成一下的修复脚本：
/*
Use master
Alter database [MyTempDB] Modify File (Name=Test_data,Newname=MyTempDB_data,Filename='D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyTempDB_data.mdf')
Alter database [MyTempDB] Set Offline
Exec xp_cmdshell 'rename "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Test_data.mdf" MyTempDB_data.mdf'
Alter database [MyTempDB] Set Online
Alter database [MyTempDB] Modify File (Name=Test_log,Newname=MyTempDB_log,Filename='D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyTempDB_log.ldf')
Alter database [MyTempDB] Set Offline
Exec xp_cmdshell 'rename "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Test_log.ldf" MyTempDB_log.ldf'
Alter database [MyTempDB] Set Online
*/
