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 '+CHAR(39)+@dbname+CHAR(39)+',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)
print @sqlexec

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) 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)
BEGIN

set @sqlexec='alter database '+@dbname+' modify file (name='+rtrim(@dataname)+',NEWNAME='+@dbname+')';
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


---------------------------------------------------------------------------------------------------------
查询
select * from GuangzhouWebBase..sysfiles where charindex('MDF',filename)>0
select * from GuangzhouWebBase..sysfiles where charindex('LDF',filename)>0