Rename objects inside stored procedures and views

Just in case this database object renaming project is in response to
complaints from the application developers for more appropriate and
consistent column names, you can simply alias columns returned from a view or
stored procedure.
For example:

select
col1 as OrderID,
col2 as OrderDate,
...

You can also abstract the naming conventions of your existing tables using
views and a new schema. Create a new schema, and then within that schema
create a view for each table in the original schema which aliases columns as
needed. Once done, you can then modify your application, views, and
procedures to reference the new schema name rather than the base tables.
These views are also updatable and insertable just like the tables in the old
schema. The good thing about this approach is that the existing tables with
their original column names are left in place and not modified, so you can
still reference them using the old schema name. If your work for a large IT
department, then no doubt there are probably developers in other groups or
departments that use the database and want to continue using the original
schema going forward. Using the technique described above, they don't need to
change anything.

For example:

create schema [Sales2]
GO

create view Sales2.Currency with schemabinding
as
select
CurrencyCode,
Name as CurrencyName,
ModifiedDate as ModTime
from
Sales.Currency
GO

update Sales2.Currency set ModTime = getutcdate() where CurrencyName = 'Lek'

(1 row(s) affected)

select top 3 * from Sales2.Currency

CurrencyCode CurrencyName ModTime
---------------- ---------------- -----------
AED Emirati Dirham 1998-06-01 00:00:00.000
AFA Afghani 1998-06-01 00:00:00.000
ALL Lek 2008-07-24 13:36:35.037

The schema [Sales] with the original column names is still there, and it
reflects the update we just made via [Sales2].

select top 3 * from Sales.Currency

CurrencyCode Name ModifiedDate
AED Emirati Dirham 1998-06-01 00:00:00.000
AFA Afghani 1998-06-01 00:00:00.000
ALL Lek 2008-07-24 13:36:35.037

If you still decide to go the route of modifying physical table and column
names within your database, then I would reccomend using a good regular
expression search and replace tool like InfoRapid Search & Replace.


"Philipp Post" wrote:

> Imagine a database with a bad naming convention you wish to clean up -
> having some columns with the same names as the table and the
> identifier columns named as ID_<table name>.
>
> Writing a script to rename tables, columns and constraints using
> sp_rename is not a big issue having a data dictionary. However
> changing the code inside VIEWs and stored procedures is a pain having
> hundreds of them. I could look at the dependencies using
> sys.sql_dependencies but what next, if I wish to automate that task?
>
> Scripting all objects out and using a text editor was not very
> successfull due to the mentioned duplicated names for tables and
> columns, so a simple search and replace will not do it.
>
> What would you suggest to have this done in an acceptable timeframe?
> Using SQL Server 2005 Express with Management Studio Express.
>
> Thanks and brgds
>
> Philipp Post
>
 >> Stay informed about: Rename objects inside stored procedures and views 

Stuart,

> Have you tried a refactoring product like Redgate's SQL Refactor? <

I would have loved to use it, but it is not working with the express
versions. Also it is not really cheap.

Eric,

I have to confess that I am the one who is responsible for the mess
and also the one who is absolutely fed up with it after some years
creating a jungle of horribly and inconsistent named objects.
Fortunately it is a hobby project only but it has reached a state
where bad naming becomes unmanageable. My mistake was that I ported an
old project of an other developer and did not review the bad naming of
that one; later I added the new objects in a more consistent and
better way, but lots of old stuff is still left. As I can do it in
this case, I prefer cleaning it from the ground up.

> InfoRapid Search & Replace. <

I will have a look at it if that will do it. Thanks for the hint.

brgds

Philipp Post
 >> Stay informed about: Rename objects inside stored procedures and views 

A lot of times we need a change . While working on an existing database, we may need to change the database name and in some cases want to rename existing database objects. This can be done in a few seconds.

In SQL Server this can be done in this manner :

1. Renaming a database :

The SQL Server command for renaming a database is :

Syntax: EXEC sp_renamedb 'oldName', 'newName' ;

eg: Suppose we have a database named "GPSTrainees" and we want to rename it to "KLMSTrainees"
We can write :
EXEC sp_renamedb 'GPSTrainees' , 'KLMSTrainees' ;

However, though this command works on SQL Server 2000 and SQL Server 2005, it will not be supported in future versions of SQL Server. The new command that should be used for SQL Server 2005 and beyond is:

ALTER DATABASE oldName MODIFY NAME = newName ;

eg: ALTER DATABASE GPSTrainees MODIFY NAME=KLMSTrainees

http://www.mindfiresolutions.com/Renaming-Databse-Objects-859.php



EricRussel wrote:

Just in case this database object renaming project is in response to
24-Jul-08

Just in case this database object renaming project is in response to
complaints from the application developers for more appropriate and
consistent column names, you can simply alias columns returned from a view or
stored procedure.
For example:

select
col1 as OrderID,
col2 as OrderDate,
...

You can also abstract the naming conventions of your existing tables using
views and a new schema. Create a new schema, and then within that schema
create a view for each table in the original schema which aliases columns as
needed. Once done, you can then modify your application, views, and
procedures to reference the new schema name rather than the base tables.
These views are also updatable and insertable just like the tables in the old
schema. The good thing about this approach is that the existing tables with
their original column names are left in place and not modified, so you can
still reference them using the old schema name. If your work for a large IT
department, then no doubt there are probably developers in other groups or
departments that use the database and want to continue using the original
schema going forward. Using the technique described above, they don't need to
change anything.

For example:

create schema [Sales2]
GO

create view Sales2.Currency with schemabinding
as
select
CurrencyCode,
Name as CurrencyName,
ModifiedDate as ModTime
from
Sales.Currency
GO

update Sales2.Currency set ModTime = getutcdate() where CurrencyName = 'Lek'

(1 row(s) affected)

select top 3 * from Sales2.Currency

CurrencyCode CurrencyName ModTime
---------------- ---------------- -----------
AED Emirati Dirham 1998-06-01 00:00:00.000
AFA Afghani 1998-06-01 00:00:00.000
ALL Lek 2008-07-24 13:36:35.037

The schema [Sales] with the original column names is still there, and it
reflects the update we just made via [Sales2].

select top 3 * from Sales.Currency

CurrencyCode Name ModifiedDate
AED Emirati Dirham 1998-06-01 00:00:00.000
AFA Afghani 1998-06-01 00:00:00.000
ALL Lek 2008-07-24 13:36:35.037

If you still decide to go the route of modifying physical table and column
names within your database, then I would reccomend using a good regular
expression search and replace tool like InfoRapid Search & Replace.


"Philipp Post" wrote:

Previous Posts In This Thread:

On Thursday, July 24, 2008 9:57 AM
EricRussel wrote:

Just in case this database object renaming project is in response to
Just in case this database object renaming project is in response to
complaints from the application developers for more appropriate and
consistent column names, you can simply alias columns returned from a view or
stored procedure.
For example:

select
col1 as OrderID,
col2 as OrderDate,
...

You can also abstract the naming conventions of your existing tables using
views and a new schema. Create a new schema, and then within that schema
create a view for each table in the original schema which aliases columns as
needed. Once done, you can then modify your application, views, and
procedures to reference the new schema name rather than the base tables.
These views are also updatable and insertable just like the tables in the old
schema. The good thing about this approach is that the existing tables with
their original column names are left in place and not modified, so you can
still reference them using the old schema name. If your work for a large IT
department, then no doubt there are probably developers in other groups or
departments that use the database and want to continue using the original
schema going forward. Using the technique described above, they don't need to
change anything.

For example:

create schema [Sales2]
GO

create view Sales2.Currency with schemabinding
as
select
CurrencyCode,
Name as CurrencyName,
ModifiedDate as ModTime
from
Sales.Currency
GO

update Sales2.Currency set ModTime = getutcdate() where CurrencyName = 'Lek'

(1 row(s) affected)

select top 3 * from Sales2.Currency

CurrencyCode CurrencyName ModTime
---------------- ---------------- -----------
AED Emirati Dirham 1998-06-01 00:00:00.000
AFA Afghani 1998-06-01 00:00:00.000
ALL Lek 2008-07-24 13:36:35.037

The schema [Sales] with the original column names is still there, and it
reflects the update we just made via [Sales2].

select top 3 * from Sales.Currency

CurrencyCode Name ModifiedDate
AED Emirati Dirham 1998-06-01 00:00:00.000
AFA Afghani 1998-06-01 00:00:00.000
ALL Lek 2008-07-24 13:36:35.037

If you still decide to go the route of modifying physical table and column
names within your database, then I would reccomend using a good regular
expression search and replace tool like InfoRapid Search & Replace.


"Philipp Post" wrote:

On Saturday, July 26, 2008 2:44 PM
Philipp Post wrote:

Rename objects inside stored procedures and views
Imagine a database with a bad naming convention you wish to clean up -
having some columns with the same names as the table and the
identifier columns named as ID_<table name>.

Writing a script to rename tables, columns and constraints using
sp_rename is not a big issue having a data dictionary. However
changing the code inside VIEWs and stored procedures is a pain having
hundreds of them. I could look at the dependencies using
sys.sql_dependencies but what next, if I wish to automate that task?

Scripting all objects out and using a text editor was not very
successfull due to the mentioned duplicated names for tables and
columns, so a simple search and replace will not do it.

What would you suggest to have this done in an acceptable timeframe?
Using SQL Server 2005 Express with Management Studio Express.

Thanks and brgds

Philipp Post

On Saturday, July 26, 2008 2:44 PM
Stuart Ainsworth wrote:

Re: Rename objects inside stored procedures and views
On Jul 24, 6:59=A0am, Philipp Post wrote:

Have you tried a refactoring product like Redgate's SQL Refactor?

http://www.red-gate.com/products/SQL_Refactor/index.htm

Not sure if it will help you (depends on how messy the code is), but
it might be worth a shot.

HTH,
Stu

On Saturday, July 26, 2008 2:45 PM
Philipp Post wrote:

Stuart,I would have loved to use it, but it is not working with the
Stuart,


I would have loved to use it, but it is not working with the express
versions. Also it is not really cheap.

Eric,

I have to confess that I am the one who is responsible for the mess
and also the one who is absolutely fed up with it after some years
creating a jungle of horribly and inconsistent named objects.
Fortunately it is a hobby project only but it has reached a state
where bad naming becomes unmanageable. My mistake was that I ported an
old project of an other developer and did not review the bad naming of
that one; later I added the new objects in a more consistent and
better way, but lots of old stuff is still left. As I can do it in
this case, I prefer cleaning it from the ground up.


I will have a look at it if that will do it. Thanks for the hint.

brgds

Philipp Post


Submitted via EggHeadCafe - Software Developer Portal of Choice
Sending SMTP email from within BizTalk Orchestration
http://www.eggheadcafe.com/tutorials/aspnet/9dd0f346-baf9-4674-a50f-17...45b26bc
 >> Stay informed about: Rename objects inside stored procedures and views 

eliza
What is your question?

<eliza> wrote in message
>A lot of times we need a change . While working on an existing database,
>we may need to change the database name and in some cases want to rename
>existing database objects. This can be done in a few seconds.
>
> In SQL Server this can be done in this manner :
>
> 1. Renaming a database :
>
> The SQL Server command for renaming a database is :
>
> Syntax: EXEC sp_renamedb 'oldName', 'newName' ;
>
> eg: Suppose we have a database named "GPSTrainees" and we want to
> rename it to "KLMSTrainees"
> We can write :
> EXEC sp_renamedb 'GPSTrainees' , 'KLMSTrainees' ;
>
> However, though this command works on SQL Server 2000 and SQL Server
> 2005, it will not be supported in future versions of SQL Server. The new
> command that should be used for SQL Server 2005 and beyond
> is:
>
> ALTER DATABASE oldName MODIFY NAME = newName ;
>
> eg: ALTER DATABASE GPSTrainees MODIFY NAME=KLMSTrainees
>
> http://www.mindfiresolutions.com/Renaming-Databse-Objects-859.php
>
>
>
> EricRussel wrote:
>
> Just in case this database object renaming project is in response to
> 24-Jul-08
>
> Just in case this database object renaming project is in response to
> complaints from the application developers for more appropriate and
> consistent column names, you can simply alias columns returned from a view
> or
> stored procedure.
> For example:
>
> select
> col1 as OrderID,
> col2 as OrderDate,
> ...
>
> You can also abstract the naming conventions of your existing tables using
> views and a new schema. Create a new schema, and then within that schema
> create a view for each table in the original schema which aliases columns
> as
> needed. Once done, you can then modify your application, views, and
> procedures to reference the new schema name rather than the base tables.
> These views are also updatable and insertable just like the tables in the
> old
> schema. The good thing about this approach is that the existing tables
> with
> their original column names are left in place and not modified, so you can
> still reference them using the old schema name. If your work for a large
> IT
> department, then no doubt there are probably developers in other groups or
> departments that use the database and want to continue using the original
> schema going forward. Using the technique described above, they don't need
> to
> change anything.
>
> For example:
>
> create schema [Sales2]
> GO
>
> create view Sales2.Currency with schemabinding
> as
> select
> CurrencyCode,
> Name as CurrencyName,
> ModifiedDate as ModTime
> from
> Sales.Currency
> GO
>
> update Sales2.Currency set ModTime = getutcdate() where CurrencyName =
> 'Lek'
>
> (1 row(s) affected)
>
> select top 3 * from Sales2.Currency
>
> CurrencyCode CurrencyName ModTime
> ---------------- ---------------- -----------
> AED Emirati Dirham 1998-06-01 00:00:00.000
> AFA Afghani 1998-06-01 00:00:00.000
> ALL Lek 2008-07-24 13:36:35.037
>
> The schema [Sales] with the original column names is still there, and it
> reflects the update we just made via [Sales2].
>
> select top 3 * from Sales.Currency
>
> CurrencyCode Name ModifiedDate
> AED Emirati Dirham 1998-06-01 00:00:00.000
> AFA Afghani 1998-06-01 00:00:00.000
> ALL Lek 2008-07-24 13:36:35.037
>
> If you still decide to go the route of modifying physical table and column
> names within your database, then I would reccomend using a good regular
> expression search and replace tool like InfoRapid Search & Replace.
>
>
> "Philipp Post" wrote:
>
> Previous Posts In This Thread:
>
> On Thursday, July 24, 2008 9:57 AM
> EricRussel wrote:
>
> Just in case this database object renaming project is in response to
> Just in case this database object renaming project is in response to
> complaints from the application developers for more appropriate and
> consistent column names, you can simply alias columns returned from a view
> or
> stored procedure.
> For example:
>
> select
> col1 as OrderID,
> col2 as OrderDate,
> ...
>
> You can also abstract the naming conventions of your existing tables using
> views and a new schema. Create a new schema, and then within that schema
> create a view for each table in the original schema which aliases columns
> as
> needed. Once done, you can then modify your application, views, and
> procedures to reference the new schema name rather than the base tables.
> These views are also updatable and insertable just like the tables in the
> old
> schema. The good thing about this approach is that the existing tables
> with
> their original column names are left in place and not modified, so you can
> still reference them using the old schema name. If your work for a large
> IT
> department, then no doubt there are probably developers in other groups or
> departments that use the database and want to continue using the original
> schema going forward. Using the technique described above, they don't need
> to
> change anything.
>
> For example:
>
> create schema [Sales2]
> GO
>
> create view Sales2.Currency with schemabinding
> as
> select
> CurrencyCode,
> Name as CurrencyName,
> ModifiedDate as ModTime
> from
> Sales.Currency
> GO
>
> update Sales2.Currency set ModTime = getutcdate() where CurrencyName =
> 'Lek'
>
> (1 row(s) affected)
>
> select top 3 * from Sales2.Currency
>
> CurrencyCode CurrencyName ModTime
> ---------------- ---------------- -----------
> AED Emirati Dirham 1998-06-01 00:00:00.000
> AFA Afghani 1998-06-01 00:00:00.000
> ALL Lek 2008-07-24 13:36:35.037
>
> The schema [Sales] with the original column names is still there, and it
> reflects the update we just made via [Sales2].
>
> select top 3 * from Sales.Currency
>
> CurrencyCode Name ModifiedDate
> AED Emirati Dirham 1998-06-01 00:00:00.000
> AFA Afghani 1998-06-01 00:00:00.000
> ALL Lek 2008-07-24 13:36:35.037
>
> If you still decide to go the route of modifying physical table and column
> names within your database, then I would reccomend using a good regular
> expression search and replace tool like InfoRapid Search & Replace.
>
>
> "Philipp Post" wrote:
>
> On Saturday, July 26, 2008 2:44 PM
> Philipp Post wrote:
>
> Rename objects inside stored procedures and views
> Imagine a database with a bad naming convention you wish to clean up -
> having some columns with the same names as the table and the
> identifier columns named as ID_<table name>.
>
> Writing a script to rename tables, columns and constraints using
> sp_rename is not a big issue having a data dictionary. However
> changing the code inside VIEWs and stored procedures is a pain having
> hundreds of them. I could look at the dependencies using
> sys.sql_dependencies but what next, if I wish to automate that task?
>
> Scripting all objects out and using a text editor was not very
> successfull due to the mentioned duplicated names for tables and
> columns, so a simple search and replace will not do it.
>
> What would you suggest to have this done in an acceptable timeframe?
> Using SQL Server 2005 Express with Management Studio Express.
>
> Thanks and brgds
>
> Philipp Post
>
> On Saturday, July 26, 2008 2:44 PM
> Stuart Ainsworth wrote:
>
> Re: Rename objects inside stored procedures and views
> On Jul 24, 6:59=A0am, Philipp Post wrote:
>
> Have you tried a refactoring product like Redgate's SQL Refactor?
>
> http://www.red-gate.com/products/SQL_Refactor/index.htm
>
> Not sure if it will help you (depends on how messy the code is), but
> it might be worth a shot.
>
> HTH,
> Stu
>
> On Saturday, July 26, 2008 2:45 PM
> Philipp Post wrote:
>
> Stuart,I would have loved to use it, but it is not working with the
> Stuart,
>
>
> I would have loved to use it, but it is not working with the express
> versions. Also it is not really cheap.
>
> Eric,
>
> I have to confess that I am the one who is responsible for the mess
> and also the one who is absolutely fed up with it after some years
> creating a jungle of horribly and inconsistent named objects.
> Fortunately it is a hobby project only but it has reached a state
> where bad naming becomes unmanageable. My mistake was that I ported an
> old project of an other developer and did not review the bad naming of
> that one; later I added the new objects in a more consistent and
> better way, but lots of old stuff is still left. As I can do it in
> this case, I prefer cleaning it from the ground up.
>
>
> I will have a look at it if that will do it. Thanks for the hint.
>
> brgds
>
> Philipp Post
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> Sending SMTP email from within BizTalk Orchestration
> http://www.eggheadcafe.com/tutorials/aspnet/9dd0f346-baf9-4674-a50f-17...45b26bc
 >> Stay informed about: Rename objects inside stored procedures and views