How can I modify the default file name format for auto generated script files in SQL Server Management Studio?

Question

Our database contains about 600 stored procedures and functions. We are trying to better manage changes to the database scripts without having to purchase any additional software.

We would like to use the > Tasks > Generate Scripts function in SQL Server Management Studio to automatically generate individual files for every stored procedure and function in the database.

This article pretty much sums up the steps we take to achieve this: http://earljon.wordpress.com/2008/04/10/sql-server-2005-scripting-each-objects-to-a-separate-file/

The only issue we are having with this is with the default naming convention Management Studio uses for the file name. It suffixes the type of script to the end of the file name like so dbo.Name.StoredProcedure.sql or dbo.Name.UserDefinedFunction.sql. We want to change it so the file name is SP_Name.sql and FN_Name.sql.

Is it possible to customize the output file name format within SQL Server Management Studio? We would prefer that the wizard output this file name convention, rather than having to use a separate piece of software to batch rename them.

Thanks, Nick

Answer 1

powershell script rename.ps1, run in the folder containing the files you want to rename:

Get-ChildItem -Filter "*.Table.sql" |Rename-Item -NewName {$_.name -replace '.Table.sql','.sql'}
Get-ChildItem -Filter "*.UserDefinedFunction.sql" |Rename-Item -NewName {$_.name -replace '.UserDefinedFunction.sql','.sql'}
Get-ChildItem -Filter "*.StoredProcedure.sql" |Rename-Item -NewName {$_.name -replace '.StoredProcedure.sql','.sql'}
Get-ChildItem -Filter "*.View.sql" |Rename-Item -NewName {$_.name -replace '.View.sql','.sql'}

Get-ChildItem -Filter ".Role.sql" |Rename-Item -NewName {$_.name -replace '.Role.sql','.sql'} Get-ChildItem -Filter ".Schema.sql" |Rename-Item -NewName {$.name -replace '.Schema.sql','.sql'} Get-ChildItem -Filter "*.Synonym.sql" |Rename-Item -NewName {$.name -replace '.Synonym.sql','.sql'} Get-ChildItem -Filter ".UserDefinedTableType.sql" |Rename-Item -NewName {$_.name -replace '.UserDefinedTableType.sql','.sql'} Get-ChildItem -Filter ".UserDefinedTableType.sql" |Rename-Item -NewName {$_.name -replace '.UserDefinedTableType.sql','.sql'}