话不多说,直接上脚本。
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