Rename scripted db objects

How can I rename all scripted object files in a folder, to replace 'dbo.' with the object type (Table, view..etc) and remove the appended object type (.Table, .view ..etc) from the file name

As an example: I have 'C:Tablesdbo.User.Table.SQL' I want to change that to 'C:TablesTable User.SQL'

I tried using xp_cmdshell with rename, but get an 'Access is denied' message. My Id has sysadmin access on the server and full access to the file.

TIA

asked Mar 16 '12 at 08:25 PM in Default

rfonseca
0 ● 1 ● 1 ● 1

(comments are locked)

2 answers:

does the sql server account have full access to the file? xp_cmdshell runs in the sql server process, so it uses the same account as the sql server, not the individual executing the query.

Run the following to see the user account that needs access:

exec master..xp_cmdshell 'whoami';

answered Mar 16 '12 at 09:06 PM

KenJ
12.4k ● 2 ● 10

(comments are locked)

Thanks Ken. I found the account that is running the query and granted it Full Access to the file, but I still get the same message. Any other suggestions, please Thanks..

answered Mar 19 '12 at 04:05 PM

rfonseca
0 ● 1 ● 1 ● 1

is there encryption on any of the files (can you read one with xp_cmdshell), or could they still be held open by another process? You can check for handles on the files using Process Explorer - http://technet.microsoft.com/en-us/sysinternals/bb896653

Is there anything in the error message besides access denied? Can you post your script?

Mar 19 '12 at 06:23 PM KenJ

(comments are locked)