Generate Script to Modify Logical name of databases


–Generate a set of Alter Database command to modify Logical name of Database

–Jingyang Li 12/07/2011

SELECT CASE

WHEN a.name <> b.name

AND a.name + ‘_log’ <> b.name

THEN ‘ALTER DATABASE [‘ + a.name +

‘] MODIFY FILE (NAME= [‘ + b.name + ‘] ,NEWNAME= [‘ +

CASE WHEN fileid = 1 THEN a.name +’]’ ELSE a.name + ‘_log]’ END + ‘ )’

ELSE ”

END

FROM sys.sysdatabases a

INNER JOIN sys.sysaltfiles b

ON a.dbid = b.dbid

WHERE a.dbid > 4

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s