Renaming a Table

Is there a way to rename a table in a database without breaking all the
relationships, views etc.?
Is there any procedure which would search the DB for references to the old
table name and change them to the new one?
ThanksTo display recorded dependencies you can use sp_depends. This may not list
everything though. To be sure, you could use EM to generate a script of all
objects in the database and then do a textual search on the script. An an
interim measure, you could rename the table then create a view with the old
tablename as its name which does a select * from the new tablename, while
you point the other objects towards the new table.
HTH,
Paul Ibison

Renaming a Table

Is there a way to rename a table in a database without breaking all the
relationships, views etc.?
Is there any procedure which would search the DB for references to the old
table name and change them to the new one?
Thanks
To display recorded dependencies you can use sp_depends. This may not list
everything though. To be sure, you could use EM to generate a script of all
objects in the database and then do a textual search on the script. An an
interim measure, you could rename the table then create a view with the old
tablename as its name which does a select * from the new tablename, while
you point the other objects towards the new table.
HTH,
Paul Ibison
sql

Renaming a Table

Is there a way to rename a table in a database without breaking all the
relationships, views etc.?
Is there any procedure which would search the DB for references to the old
table name and change them to the new one?
ThanksTo display recorded dependencies you can use sp_depends. This may not list
everything though. To be sure, you could use EM to generate a script of all
objects in the database and then do a textual search on the script. An an
interim measure, you could rename the table then create a view with the old
tablename as its name which does a select * from the new tablename, while
you point the other objects towards the new table.
HTH,
Paul Ibison|||check out www.dbghost.com
>--Original Message--
>Is there a way to rename a table in a database without
breaking all the
>relationships, views etc.?
>Is there any procedure which would search the DB for
references to the old
>table name and change them to the new one?
>Thanks
>
>.
>

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
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 a view that has dependent views?

SQL 2000 server: I want to standardize my naming scheme. I have views that
have other views dependent on them. I would like to rename some of these
without breaking the dependent views. It appears that sp_rename doesn't do
the trick.
Also, I'm confused about this comment in BOL: "Important After renaming
stored procedures and views, flush the procedure cache to ensure all
dependent stored procedures and views are recompiled."
I ran some tests, and it looks like sp_rename doesn't fix dependent views
that use a view that gets renamed, so what is this comment about?
Thanks. Any comments and suggestions will be appreciated.
David WalkerDavid
No, sp_rename does not go through all the dependent procedures, views and triggers and change the old view name to the new view name. You need to do that yourself. If you are relying on the info in sysdepends, DO NOT. You can create a parent stored procedure that references a child/sub procedure before the child is created. SQL will yell at you, but it will do it. You can create the child next and all will be fine
You can search for objects that reference your old view name with
SELECT OBJECT_NAME(id) FROM syscomments WHERE text LIKE '%oldviewname%
in every database that may reference the old view
The procedure cache is an area of the SQL Server memory that hold the execution plans of stored procedures and views. You can just try your changes after you implement them to see if it works, which you probably should. If some erro occurs run DBCC FREEPROCCACHE
Norman

Rename a view that has dependent views?

SQL 2000 server: I want to standardize my naming scheme. I have views that
have other views dependent on them. I would like to rename some of these
without breaking the dependent views. It appears that sp_rename doesn't do
the trick.
Also, I'm confused about this comment in BOL: "Important After renaming
stored procedures and views, flush the procedure cache to ensure all
dependent stored procedures and views are recompiled."
I ran some tests, and it looks like sp_rename doesn't fix dependent views
that use a view that gets renamed, so what is this comment about?
Thanks. Any comments and suggestions will be appreciated.
David WalkerDavid,
No, sp_rename does not go through all the dependent procedures, views and tr
iggers and change the old view name to the new view name. You need to do tha
t yourself. If you are relying on the info in sysdepends, DO NOT. You can cr
eate a parent stored proced
ure that references a child/sub procedure before the child is created. SQL w
ill yell at you, but it will do it. You can create the child next and all wi
ll be fine.
You can search for objects that reference your old view name with:
SELECT OBJECT_NAME(id) FROM syscomments WHERE text LIKE '%oldviewname%'
in every database that may reference the old view.
The procedure cache is an area of the SQL Server memory that hold the execut
ion plans of stored procedures and views. You can just try your changes afte
r you implement them to see if it works, which you probably should. If some
erro occurs run DBCC FREEPR
OCCACHE.
Norman

removing and adding objects from publication

I am replicating all tables and views in a database with snapshot
replication. It is giving an error because View2 is dependent on View1 but it
is trying to create View2 first. I was going to delete and recreate View2 so
it would come after View1 but when I went to delete View2 I got an error
saying that I couldn't delete the view because it is being used by
replication. Is there any way I can delete and recreate View2 without having
to delete all of my replication setup? Is there an easy way to remove and add
objects to a publication? Thanks!
Thanks Paul - I see that this will help me delete the view. When it is
recreated how can I add it back into the publication? Thanks!
"Paul Ibison" wrote:

> You should be able to use this type of approach:
> exec sp_dropsubscription @.publication
> = 'PDS_to_Cab_Tables'
> , @.article = 'SNAPSHOT_RELEASE_INFO'
> , @.subscriber = 'RSCOMPUTERXXX'
> , @.destination_db = 'delivery'
>
> for each subscriber, then...
> exec sp_droparticle @.publication = 'PDS_to_Cab_Tables'
> , @.article = 'SNAPSHOT_RELEASE_INFO'
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||sp_addarticle and sp_addsubscription will do it.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)