sqlserver 修改全部库逻辑文件名

平时大家不太注意库逻辑文件名，查每个库的sysfiles表都有，一般是两个文件，一个数据文件，一个日志文件
create database oa;
这样建的库 数据文件名为 oa_data 对应一个物理文件名如d:\\msql\\data\\oa.mdf,日志文件 oa_log对应物理文件名为d:\\msql\\data\\oa.ldf,但你如果用这个库做了个备份，再用那个备份还原一个oa_test 数据库，那逻辑文件名默认不是oa_test了而还是oa_data\\oa_log这是从备份集中得来的，下面例子是把这个sqlserver服务器上所有的库逻辑文件名都改成和数据库一致的。
开始
DECLARE
@dataname VARCHAR(100),  @logname  VARCHAR(100),  @dbname   VARCHAR(100),@sqlexec varchar(2000)
DECLARE dbs_cur CURSOR  FOR
SELECT   name
FROM     master..sysdatabases
WHERE    dbid > 4
ORDER BY name;
-- drop table db_info
if (not exists (select * from master..sysobjects where NAME='DB_INFO'))
begin
create table DB_INFO
(dbname varchar(100)
,dataname varchar(100)
,logname varchar(100)
)
end;
DELETE master..DB_INFO;
OPEN dbs_cur;
FETCH NEXT FROM dbs_cur INTO @dbname;
WHILE (@@FETCH_STATUS = 0)
BEGIN
set @sqlexec='insert into master..DB_INFO
select '''+@dbname+''',max(case when fileid=1 then name end)as datafile, max(case when fileid=2 then name end)as logfile
from '+@dbname+'..sysfiles'
exec (@sqlexec)
/*
exec ('USE  '+ @dbname)
SELECT @dataname=File_name(1)
SELECT @logname=File_name(2)

IF upper(@dataname) <> upper(@dbname)
BEGIN
--alter database icme2_xian modify file (name=@dataname,NEWNAME=@dbname);
PRINT @dbname + '---datafile:' + @dataname
END
IF lower(@logname) <> lower(@dbname + '_log')
BEGIN
--alter database icme2_xian modify file (name=@logname,NEWNAME=@dbname + '_log')
PRINT @dbname + '---log_file:' + @logname
END
*/
FETCH NEXT FROM dbs_cur  INTO @dbname
END
CLOSE dbs_cur
DEALLOCATE dbs_cur

declare dbinfo_cur cursor for
select * from master..DB_INFO where lower(dataname)<>lower(dbname+'_data') or lower(logname)<>lower(dbname+'_log')
OPEN dbinfo_cur;
FETCH NEXT FROM dbinfo_cur INTO @dbname,@dataname,@logname
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF lower(@dataname)<>lower(@dbname+'_data')
BEGIN
set @sqlexec='alter database '+@dbname+' modify file (name='+rtrim(@dataname)+',NEWNAME='+@dbname+'_data)';
print @sqlexec
exec (@sqlexec)
END
IF lower(@logname) <> lower(@dbname + '_log')
BEGIN
set @sqlexec='alter database '+@dbname+' modify file (name='+rtrim(@logname)+',NEWNAME='+@dbname+ '_log)'
print @sqlexec
exec (@sqlexec);
END
FETCH NEXT FROM dbinfo_cur INTO @dbname,@dataname,@logname
END
close dbinfo_cur
DEALLOCATE dbinfo_cur
结束

本文来源https://www.modb.pro/db/20184