Mobile Management For SQL Server(s!) - Siccolo - SQL Management Tool
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.

Article keywords: create procedure , set nocount on, rename , xp_cmdshell


Back To Articles Page

Free Mobile Management For SQL Server(s!) - Siccolo - SQL Management ToolQuestions? Suggestions? Concerns? - email me to siccolo_mobile_management@yahoo.com    Greg Dubinovsky 2006
or share your thoughts at Siccolo Blog

Web being sponsor - Mid-Atlantic Processing. Well being sponsor - Clarity MediSpa. Hairless sponsor - Clarity MediSpa Laser Hair Removal.
Siccolo - SQL Server Management Tool For Mobile Devices is packed with built-in functionality and tools. Siccolo delivers a rich set of management tools for both DBAs and sys admins. SQL Server management has always been an area of DBA concern. The new Management Tool For Mobile Devices - Siccolo - has simple "Enterprise Manager" and the "Query Analyzer". Siccolo is a management tool for the MS SQL Server with administration capabilities and a database query tool. The administration features provide users the ability to browse database structures. An integrated query tool allows users to quickly create, edit and execute SQL queries and scripts. Siccolo also provides an export tool to allow users to easily save and email execution results. Siccolo helps database professionals save time and increase their productivity by utilizing a more efficient approach to database management - use their Windows Mobile empowered device while sipping margarita on the beach For increased security, Siccolo is configured to run under SSL with IIS authentication.