Main |
How to Mass Rename Files from SQL using xp_cmdshell and rename command
...by Siccolo...
Engineered to help you extend your support from mobile devices for Microsoft SQL Servers, Siccolo mobile management tool is a portable administration tool for Microsoft SQL Server.
Siccolo is the premier GUI admin tool for Microsoft SQL Server management, control and development.
Siccolo gives you wide opportunities to perform all the necessary database admin
routines such as creating, editing, dropping database objects;
moreover, you can build queries, execute queries and SQL scripts,
view data, export data, manage SQL Server users and their privileges,
add jobs and alerts, and use a lot of other admin tools implemented to
make your work with SQL Server highly enjoyable and efficient.
If you need the job done right and on time, do it with Siccolo on your team.
Siccolo provides you with a number of easy-to-use features for performing the required data manipulation
and server management easily and quickly.
|
|
In many cases you a have a need to mass rename file in a certain location
|
|
Using xp_cmdshell -
eXtended stored procedure provided by Microsoft and stored in the master database.
This procedure allows you to issue operating system commands directly to the Windows command shell via T-SQL code.
If needed the output of these commands will be returned to the calling routine.
create procedure sp_MassFileNameRename
(
@path varchar(555)
, @find varchar(255)
, @replace varchar(255)
)
as
-- exec sp_MassFileNameRename 'd:\temp\test', 'rrr', 'aaa'
set nocount on
-- normalize path
if right(@path,1)!='\' set @path = @path+'\'
declare @command varchar(666)
set @command = 'dir ' + @path + '*' + @find + '*' + ' /B'
print @command
create table #tmp_dir_list (file_name_listing varchar(1000))
insert into #tmp_dir_list
exec master.dbo.xp_cmdshell @command
-- clean up directory list:
delete #tmp_dir_list where isnull(file_name_listing,'')=''
delete #tmp_dir_list where isnull(file_name_listing,'')='File Not Found'
select * from #tmp_dir_list
-- for each file in list - rename
declare @file_name varchar(555)
, @new_file_name varchar(999)
declare file_list cursor
for select file_name_listing
from #tmp_dir_list
for read only
open file_list
fetch next from file_list into @file_name
while @@fetch_status=0
begin
set @new_file_name = replace(@file_name, @find, @replace)
set @file_name = @path + @file_name
set @command = 'rename "' + @file_name + '" "' + @new_file_name + '"'
print @command
exec master.dbo.xp_cmdshell @command , NO_OUTPUT
fetch next from file_list into @file_name
end
close file_list
deallocate file_list
drop table #tmp_dir_list
set nocount off
Side Note:
xp_cmdshell - eXtended SQL stored procedure.
Executes a given command string as an operating-system command shell and returns any output as rows of text. Grants nonadministrative users permissions to execute xp_cmdshell.
xp_cmdshell operates synchronously. Control is not returned until the command shell command completes.
Where xp_cmdshell {'command_string'} [, no_output]
For example, to retireve all the folders for a given disk path:
create table #tmp (directory_name varchar(255))
insert into #tmp
exec master.dbo.xp_cmdshell "dir d:\ /B /A:D /S"
select * from #tmp --for xml auto
drop table #tmp
or to get server information:
exec master.dbo.xp_cmdshell "net statistics server"
Side Note:
rename - Renames a file or files.
RENAME [drive:][path]filename1 filename2.
REN [drive:][path]filename1 filename2.
Note that you cannot specify a new drive or path for your destination file.
|
|