Renaming a column and replacing references to the old column name

Is there a utility out there to allow be able to rename a column in a table
and then scan the database for any objects (procs, functions, views) that us
e
this column and rename them as well. we have a db with some badly named
columns that are confusing the hell out of developers and want to rename
globally.That would be not easy to do this automatically, if two columns have the sam
e
name (refering to your poor designed database) you can do something like a
search % replace). The safest way would be to iterate (manually) rhough all
the object and change that.
Sorry about that.
"markbate" wrote:

> Is there a utility out there to allow be able to rename a column in a tabl
e
> and then scan the database for any objects (procs, functions, views) that
use
> this column and rename them as well. we have a db with some badly named
> columns that are confusing the hell out of developers and want to rename
> globally.|||You can use Enterprise Manager to generate creation scripts for all
objects, then search and replace the names and re-run the scripts.
I recommend you keep all your scripts in a source control system - then
you minimize the need to go back to the database to find the objects to
be changed.
David Portas
SQL Server MVP
--|||This is how I do this:
For a start I have all my source code within source control.
I change the create statement in question and run a build using to see what
breaks, then I can check out all the relevant scripts and make my changes
until I have no build breaks. I can then check everything back in and create
a custom script to rename the column, adding this to source control. I use D
B
Ghost (http://www.dbghost.com) and not just because I created it but because
this is the only fool proof way of introducing changes that don't break the
system with the added bonus of all the history of changes in my source
control.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"markbate" wrote:

> Is there a utility out there to allow be able to rename a column in a tabl
e
> and then scan the database for any objects (procs, functions, views) that
use
> this column and rename them as well. we have a db with some badly named
> columns that are confusing the hell out of developers and want to rename
> globally.sql

Renaming a column and replacing references to the old column name

Is there a utility out there to allow be able to rename a column in a table
and then scan the database for any objects (procs, functions, views) that use
this column and rename them as well. we have a db with some badly named
columns that are confusing the hell out of developers and want to rename
globally.
That would be not easy to do this automatically, if two columns have the same
name (refering to your poor designed database) you can do something like a
search % replace). The safest way would be to iterate (manually) rhough all
the object and change that.
Sorry about that.
"markbate" wrote:

> Is there a utility out there to allow be able to rename a column in a table
> and then scan the database for any objects (procs, functions, views) that use
> this column and rename them as well. we have a db with some badly named
> columns that are confusing the hell out of developers and want to rename
> globally.
|||You can use Enterprise Manager to generate creation scripts for all
objects, then search and replace the names and re-run the scripts.
I recommend you keep all your scripts in a source control system - then
you minimize the need to go back to the database to find the objects to
be changed.
David Portas
SQL Server MVP
|||This is how I do this:
For a start I have all my source code within source control.
I change the create statement in question and run a build using to see what
breaks, then I can check out all the relevant scripts and make my changes
until I have no build breaks. I can then check everything back in and create
a custom script to rename the column, adding this to source control. I use DB
Ghost (http://www.dbghost.com) and not just because I created it but because
this is the only fool proof way of introducing changes that don't break the
system with the added bonus of all the history of changes in my source
control.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"markbate" wrote:

> Is there a utility out there to allow be able to rename a column in a table
> and then scan the database for any objects (procs, functions, views) that use
> this column and rename them as well. we have a db with some badly named
> columns that are confusing the hell out of developers and want to rename
> globally.

Renaming a column and replacing references to the old column name

Is there a utility out there to allow be able to rename a column in a table
and then scan the database for any objects (procs, functions, views) that use
this column and rename them as well. we have a db with some badly named
columns that are confusing the hell out of developers and want to rename
globally.That would be not easy to do this automatically, if two columns have the same
name (refering to your poor designed database) you can do something like a
search % replace). The safest way would be to iterate (manually) rhough all
the object and change that.
Sorry about that.
"markbate" wrote:
> Is there a utility out there to allow be able to rename a column in a table
> and then scan the database for any objects (procs, functions, views) that use
> this column and rename them as well. we have a db with some badly named
> columns that are confusing the hell out of developers and want to rename
> globally.|||example - we want to rename a column called Deleted - to Active. Then scan
all procs to see where this table.column combination is used in any procs,
functions, views etc and rename as well, hope that is a little clearer. There
must be a utility out there that does this but I cany find one. thanks
"Jens Sü�meyer" wrote:
> That would be not easy to do this automatically, if two columns have the same
> name (refering to your poor designed database) you can do something like a
> search % replace). The safest way would be to iterate (manually) rhough all
> the object and change that.
> Sorry about that.
> "markbate" wrote:
> > Is there a utility out there to allow be able to rename a column in a table
> > and then scan the database for any objects (procs, functions, views) that use
> > this column and rename them as well. we have a db with some badly named
> > columns that are confusing the hell out of developers and want to rename
> > globally.|||You can use Enterprise Manager to generate creation scripts for all
objects, then search and replace the names and re-run the scripts.
I recommend you keep all your scripts in a source control system - then
you minimize the need to go back to the database to find the objects to
be changed.
--
David Portas
SQL Server MVP
--|||Search & Replace will work if you can rely on your definitions.
You want to change the name "Active" to "Deleted" on a special table. You
run S&R that´ll will change also that columns in other which should not be
changed (You should keep a hand on the automatic process), that can bring you
some weird problems if you don´t do that in a accurate way.
HTH, jens Suessmeyer.
"markbate" wrote:
> example - we want to rename a column called Deleted - to Active. Then scan
> all procs to see where this table.column combination is used in any procs,
> functions, views etc and rename as well, hope that is a little clearer. There
> must be a utility out there that does this but I cany find one. thanks
> "Jens Sü�meyer" wrote:
> > That would be not easy to do this automatically, if two columns have the same
> > name (refering to your poor designed database) you can do something like a
> > search % replace). The safest way would be to iterate (manually) rhough all
> > the object and change that.
> >
> > Sorry about that.
> >
> > "markbate" wrote:
> >
> > > Is there a utility out there to allow be able to rename a column in a table
> > > and then scan the database for any objects (procs, functions, views) that use
> > > this column and rename them as well. we have a db with some badly named
> > > columns that are confusing the hell out of developers and want to rename
> > > globally.|||This is how I do this:
For a start I have all my source code within source control.
I change the create statement in question and run a build using to see what
breaks, then I can check out all the relevant scripts and make my changes
until I have no build breaks. I can then check everything back in and create
a custom script to rename the column, adding this to source control. I use DB
Ghost (http://www.dbghost.com) and not just because I created it but because
this is the only fool proof way of introducing changes that don't break the
system with the added bonus of all the history of changes in my source
control.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"markbate" wrote:
> Is there a utility out there to allow be able to rename a column in a table
> and then scan the database for any objects (procs, functions, views) that use
> this column and rename them as well. we have a db with some badly named
> columns that are confusing the hell out of developers and want to rename
> globally.

rename table

Hello,
I want to rename a table but this table is used into storage procs and
views. How could I rename this table in order to the new name changes into
storage pros and views?
Thanks
LN
You can't, you will have to manually change the stored procedures and views.
You can however create a view with the same name as the old table that does
a straightforward select of all the relevant columns, that will save you
from having to update everything.
Jacco Schalkwijk
SQL Server MVP
"hlne" <Helene.Zouari@.isped.u-bordeaux2.fr> wrote in message
news:OIOxLRfsEHA.3788@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I want to rename a table but this table is used into storage procs and
> views. How could I rename this table in order to the new name changes into
> storage pros and views?
> Thanks
> LN
>

rename table

Hello,
I want to rename a table but this table is used into storage procs and
views. How could I rename this table in order to the new name changes into
storage pros and views?
Thanks
LNYou can't, you will have to manually change the stored procedures and views.
You can however create a view with the same name as the old table that does
a straightforward select of all the relevant columns, that will save you
from having to update everything.
--
Jacco Schalkwijk
SQL Server MVP
"hélène" <Helene.Zouari@.isped.u-bordeaux2.fr> wrote in message
news:OIOxLRfsEHA.3788@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I want to rename a table but this table is used into storage procs and
> views. How could I rename this table in order to the new name changes into
> storage pros and views?
> Thanks
> LN
>

rename table

Hello,
I want to rename a table but this table is used into storage procs and
views. How could I rename this table in order to the new name changes into
storage pros and views?
Thanks
LNYou can't, you will have to manually change the stored procedures and views.
You can however create a view with the same name as the old table that does
a straightforward select of all the relevant columns, that will save you
from having to update everything.
Jacco Schalkwijk
SQL Server MVP
"hlne" <Helene.Zouari@.isped.u-bordeaux2.fr> wrote in message
news:OIOxLRfsEHA.3788@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I want to rename a table but this table is used into storage procs and
> views. How could I rename this table in order to the new name changes into
> storage pros and views?
> Thanks
> LN
>sql

Rename dos file name

Just a shoot in the dark here...
When SQL Server exports procs to individual files, it creatin them like
dbo.sproc.prc
Needless to say this filename is causing fits...
I've tried to do a rename to no avail
any suggestions?You mean that you don't like the way that Enterprise Mangler names the files produced by SQL-DMO ?!?! You can easily roll your own (http://support.microsoft.com/default.aspx?scid=kb;en-us;233392) if you like, or you can write a short script to rename the bundles of joy delivered by EM. While the rename script is less work, I prefer the roll your own approach myself.

-PatP|||Maybe a better question is who is the file naming causing fits, and why is it causing them those fits ?!?! Maybe I ought to finger out the problem before I start fixin' on it, eh?

-PatP

Removing Old/Unused Stored Procs....

Hello Friends,
Situation:
I have been charged with cleaning up a large legacy database.
During this process I have identified tables no longer used and removed them
from the schema.
Question:
I now have may stored procs that reference these tables and are no longer
needed.
How can I identify the procs and remove them programmatically?
Do I need to recompile each one and look for errors?
Any help at all would be most welcome.
Thanks in advance,
B.Hi
Use the sysobjects, sysdepends tables to identify all the procedures using
the given tables.Build a query and from the resultset u know hat objects nee
d
to be dropped.
Imtiaz
"Tam O'Shanter" wrote:

> Hello Friends,
> Situation:
> I have been charged with cleaning up a large legacy database.
> During this process I have identified tables no longer used and removed th
em
> from the schema.
> Question:
> I now have may stored procs that reference these tables and are no longer
> needed.
> How can I identify the procs and remove them programmatically?
> Do I need to recompile each one and look for errors?
> Any help at all would be most welcome.
>
> Thanks in advance,
> B.
>
>|||Cool idea.
However, what if the tables that have been removed are unknown?
Thanks in advance for any tips.
B.
"Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
> Hi
> Use the sysobjects, sysdepends tables to identify all the procedures using
> the given tables.Build a query and from the resultset u know hat objects
need[vbcol=seagreen]
> to be dropped.
> Imtiaz
> "Tam O'Shanter" wrote:
>
them[vbcol=seagreen]
longer[vbcol=seagreen]|||Tam O'Shanter wrote:[vbcol=seagreen]
> Cool idea.
> However, what if the tables that have been removed are unknown?
> Thanks in advance for any tips.
> B.
> "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
I was going to suggest you try and recompile all your procedures and see
which ones fail because of missing table references, but that does not
seem to work:
create table whatever (col1 int)
go
create proc whateverproc
as
select * from whatever
go
sp_depends whateverproc
go
drop table whatever
go
sp_depends whateverproc
go
drop proc whateverproc
go
If you have a list of tables you dropped, you could search syscomments
table in the database for the table names using:
Select object_name(id)
from syscomments
where text like N'%<table_name>%'
However, if your procedures are encrypted that won't work either.
--
David G.|||If you have a list of tables that you removed, then you can find out
all the stored procedures which are using the tables that were
removed.
Do a
select * from syscomments where text like '%tablename%'
this will give you a list of all the objects which were using the
table that you deleted and hence all this objects should be invalid
and hence you can delete them.
Thank You
-Pranay
"Tam O'Shanter" <Tam@.Oshanter.com> wrote in message news:<SPsWc.22207195$Id.3687174@.news.eas
ynews.com>...
> Hello Friends,
> Situation:
> I have been charged with cleaning up a large legacy database.
> During this process I have identified tables no longer used and removed th
em
> from the schema.
> Question:
> I now have may stored procs that reference these tables and are no longer
> needed.
> How can I identify the procs and remove them programmatically?
> Do I need to recompile each one and look for errors?
> Any help at all would be most welcome.
>
> Thanks in advance,
> B.

Removing Old/Unused Stored Procs....

Hello Friends,
Situation:
I have been charged with cleaning up a large legacy database.
During this process I have identified tables no longer used and removed them
from the schema.
Question:
I now have may stored procs that reference these tables and are no longer
needed.
How can I identify the procs and remove them programmatically?
Do I need to recompile each one and look for errors?
Any help at all would be most welcome.
Thanks in advance,
B.
Hi
Use the sysobjects, sysdepends tables to identify all the procedures using
the given tables.Build a query and from the resultset u know hat objects need
to be dropped.
Imtiaz
"Tam O'Shanter" wrote:

> Hello Friends,
> Situation:
> I have been charged with cleaning up a large legacy database.
> During this process I have identified tables no longer used and removed them
> from the schema.
> Question:
> I now have may stored procs that reference these tables and are no longer
> needed.
> How can I identify the procs and remove them programmatically?
> Do I need to recompile each one and look for errors?
> Any help at all would be most welcome.
>
> Thanks in advance,
> B.
>
>
|||Cool idea.
However, what if the tables that have been removed are unknown?
Thanks in advance for any tips.
B.
"Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
> Hi
> Use the sysobjects, sysdepends tables to identify all the procedures using
> the given tables.Build a query and from the resultset u know hat objects
need[vbcol=seagreen]
> to be dropped.
> Imtiaz
> "Tam O'Shanter" wrote:
them[vbcol=seagreen]
longer[vbcol=seagreen]
|||Tam O'Shanter wrote:[vbcol=seagreen]
> Cool idea.
> However, what if the tables that have been removed are unknown?
> Thanks in advance for any tips.
> B.
> "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
I was going to suggest you try and recompile all your procedures and see
which ones fail because of missing table references, but that does not
seem to work:
create table whatever (col1 int)
go
create proc whateverproc
as
select * from whatever
go
sp_depends whateverproc
go
drop table whatever
go
sp_depends whateverproc
go
drop proc whateverproc
go
If you have a list of tables you dropped, you could search syscomments
table in the database for the table names using:
Select object_name(id)
from syscomments
where text like N'%<table_name>%'
However, if your procedures are encrypted that won't work either.
David G.
|||If you have a list of tables that you removed, then you can find out
all the stored procedures which are using the tables that were
removed.
Do a
select * from syscomments where text like '%tablename%'
this will give you a list of all the objects which were using the
table that you deleted and hence all this objects should be invalid
and hence you can delete them.
Thank You
-Pranay
"Tam O'Shanter" <Tam@.Oshanter.com> wrote in message news:<SPsWc.22207195$Id.3687174@.news.easynews.com> ...
> Hello Friends,
> Situation:
> I have been charged with cleaning up a large legacy database.
> During this process I have identified tables no longer used and removed them
> from the schema.
> Question:
> I now have may stored procs that reference these tables and are no longer
> needed.
> How can I identify the procs and remove them programmatically?
> Do I need to recompile each one and look for errors?
> Any help at all would be most welcome.
>
> Thanks in advance,
> B.

Removing Old/Unused Stored Procs....

Hello Friends,
Situation:
I have been charged with cleaning up a large legacy database.
During this process I have identified tables no longer used and removed them
from the schema.
Question:
I now have may stored procs that reference these tables and are no longer
needed.
How can I identify the procs and remove them programmatically?
Do I need to recompile each one and look for errors?
Any help at all would be most welcome.
Thanks in advance,
B.Cool idea.
However, what if the tables that have been removed are unknown?
Thanks in advance for any tips.
B.
"Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
> Hi
> Use the sysobjects, sysdepends tables to identify all the procedures using
> the given tables.Build a query and from the resultset u know hat objects
need
> to be dropped.
> Imtiaz
> "Tam O'Shanter" wrote:
> > Hello Friends,
> >
> > Situation:
> >
> > I have been charged with cleaning up a large legacy database.
> > During this process I have identified tables no longer used and removed
them
> > from the schema.
> >
> > Question:
> >
> > I now have may stored procs that reference these tables and are no
longer
> > needed.
> >
> > How can I identify the procs and remove them programmatically?
> >
> > Do I need to recompile each one and look for errors?
> >
> > Any help at all would be most welcome.
> >
> >
> > Thanks in advance,
> >
> > B.
> >
> >
> >|||Tam O'Shanter wrote:
> Cool idea.
> However, what if the tables that have been removed are unknown?
> Thanks in advance for any tips.
> B.
> "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
>> Hi
>> Use the sysobjects, sysdepends tables to identify all the procedures
>> using the given tables.Build a query and from the resultset u know
>> hat objects need to be dropped.
>> Imtiaz
>> "Tam O'Shanter" wrote:
>> Hello Friends,
>> Situation:
>> I have been charged with cleaning up a large legacy database.
>> During this process I have identified tables no longer used and
>> removed them from the schema.
>> Question:
>> I now have may stored procs that reference these tables and are no
>> longer needed.
>> How can I identify the procs and remove them programmatically?
>> Do I need to recompile each one and look for errors?
>> Any help at all would be most welcome.
>>
>> Thanks in advance,
>> B.
I was going to suggest you try and recompile all your procedures and see
which ones fail because of missing table references, but that does not
seem to work:
create table whatever (col1 int)
go
create proc whateverproc
as
select * from whatever
go
sp_depends whateverproc
go
drop table whatever
go
sp_depends whateverproc
go
drop proc whateverproc
go
If you have a list of tables you dropped, you could search syscomments
table in the database for the table names using:
Select object_name(id)
from syscomments
where text like N'%<table_name>%'
However, if your procedures are encrypted that won't work either.
--
David G.|||If you have a list of tables that you removed, then you can find out
all the stored procedures which are using the tables that were
removed.
Do a
select * from syscomments where text like '%tablename%'
this will give you a list of all the objects which were using the
table that you deleted and hence all this objects should be invalid
and hence you can delete them.
Thank You
-Pranay
"Tam O'Shanter" <Tam@.Oshanter.com> wrote in message news:<SPsWc.22207195$Id.3687174@.news.easynews.com>...
> Hello Friends,
> Situation:
> I have been charged with cleaning up a large legacy database.
> During this process I have identified tables no longer used and removed them
> from the schema.
> Question:
> I now have may stored procs that reference these tables and are no longer
> needed.
> How can I identify the procs and remove them programmatically?
> Do I need to recompile each one and look for errors?
> Any help at all would be most welcome.
>
> Thanks in advance,
> B.