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:\Tables\dbo.User.Table.SQL' I want to change that to 'C:\Tables\Table 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.

more ▼

asked Mar 16, 2012 at 08:25 PM in Default

rfonseca gravatar image

0 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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';
more ▼

answered Mar 16, 2012 at 09:06 PM

KenJ gravatar image

19.8k 1 3 11

(comments are locked)
10|1200 characters needed characters left
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..
more ▼

answered Mar 19, 2012 at 04:05 PM

rfonseca gravatar image

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, 2012 at 06:23 PM KenJ
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 16, 2012 at 08:25 PM

Seen: 1120 times

Last Updated: Mar 19, 2012 at 06:24 PM