Changing column names in SSMS for SQL Server 2005

I changed the name of a column in the Summary pane of SSMS. I was a bit surprised (but pleased) to see that I could make the change there.

When I tried to use the new column name in a Query, I received the error message, "column not found."

When I used "modify" in the Object Explorer pane of SSMS I was able to see that "(int, not null)" had been appended to the column name in the table.

I don't know if this is a BUG, or an undocumented feature.

Have others here run into this? I am using SS2005, with SP1.

Dan

"int, not null" is not appended to a column name, it is part of the syntax for defining a column. If you can see the column in Object Explorer after you added and saved it you should be able to refer to it in a query.

Service Pack 2 is out ...

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx

|||

lkh,

Yes, I am sorry to say that "(int, not null)" WAS appended to the column name.

When I went into "Modify" in Object Explorer, I found the column name as

[New_name (int, not null)]. The column characteristics were "(int, not null)".

The square brackets were placed there as a result of my editing the name in the Summary pane.

So yes, "(int, not null)" was part of the new name created when I edited the name in the Summary pane.

Dan

Changing Column Names

for a 100% guarentee to pick up any defects due to the
result of an object rename, build a database from the
source code of the database. Due to problems with
sp_depends and the flaws with the information_schema
views, these are no guarentee that you have highlighted
all your potential problems. If you do a build using your
source code, then all errors will show. Of course
building has it's problems as your build order my be an
issue. These are some of the problems that our software
addresses DB Ghost. It can build a database from source
code and it takes care of all dependancies - flagging up
errors within your source code. This is change management
software and answers the question, What happens if I make
a change?
If your interested why not read our white paper on the
subject
http://www.innovartis.co.uk/pdf/Inn...An_Automated_Ap
proach_To_Do_Change_Mgt.pdf
DB Ghost - the only database change management software
for SQL Server on the market today.

>--Original Message--
>Hey folks,
>I have alot of column names in a database that I'd like
to change however
>there are alot of Stored Procedures that reference the
existing names. Is
>there a way to make SQL Server update my Stored
Procedures whenever I change
>a table's column name?
>Thanks!
>
>.
>Will it go so far as telling you that data will fail to be inserted into a
table because the foreign key dependency data is not in the foreign key
table?
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:063a01c47ad3$a4bfd520$a301280a@.phx.gbl...[vbcol=seagreen]
> for a 100% guarentee to pick up any defects due to the
> result of an object rename, build a database from the
> source code of the database. Due to problems with
> sp_depends and the flaws with the information_schema
> views, these are no guarentee that you have highlighted
> all your potential problems. If you do a build using your
> source code, then all errors will show. Of course
> building has it's problems as your build order my be an
> issue. These are some of the problems that our software
> addresses DB Ghost. It can build a database from source
> code and it takes care of all dependancies - flagging up
> errors within your source code. This is change management
> software and answers the question, What happens if I make
> a change?
> If your interested why not read our white paper on the
> subject
> http://www.innovartis.co.uk/pdf/Inn...An_Automated_Ap
> proach_To_Do_Change_Mgt.pdf
> DB Ghost - the only database change management software
> for SQL Server on the market today.
>
> to change however
> existing names. Is
> Procedures whenever I change|||yes

>--Original Message--
>Will it go so far as telling you that data will fail to
be inserted into a
>table because the foreign key dependency data is not in
the foreign key
>table?
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:063a01c47ad3$a4bfd520$a301280a@.phx.gbl...
your[vbcol=seagreen]
up[vbcol=seagreen]
management[vbcol=seagreen]
make[vbcol=seagreen]
http://www.innovartis.co.uk/pdf/Inn...An_Automated_Ap[vbcol=seagreen]
like[vbcol=seagreen]
>
>.
>

Changing Column Names

Hey folks,
I have alot of column names in a database that I'd like to change however
there are alot of Stored Procedures that reference the existing names. Is
there a way to make SQL Server update my Stored Procedures whenever I change
a table's column name?
Thanks!No, SQL Server will not rewrite your code for you ... why do you think you
can't suppress the warning from sp_rename: "Caution: Changing any part of an
object name could break scripts and stored procedures. The COLUMN was
renamed to 'brap'."?
You should be able to find most stored procedures that rely on the table by
using sp_depends, but because of deferred name resolution and depending on
the order of your script creation, you might not catch all of them.
Assuming it's not a common word like TO or DATE, you can get the list by
scanning ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES, but this isn't
flawless either.
http://www.aspfaq.com/
(Reverse address to reply.)
"John Smith" <js@.no.com> wrote in message
news:e1epiOkeEHA.2532@.TK2MSFTNGP09.phx.gbl...
> Hey folks,
> I have alot of column names in a database that I'd like to change however
> there are alot of Stored Procedures that reference the existing names. Is
> there a way to make SQL Server update my Stored Procedures whenever I
change
> a table's column name?
> Thanks!
>

Changing Column Names

Hey folks,
I have alot of column names in a database that I'd like to change however
there are alot of Stored Procedures that reference the existing names. Is
there a way to make SQL Server update my Stored Procedures whenever I change
a table's column name?
Thanks!No, SQL Server will not rewrite your code for you ... why do you think you
can't suppress the warning from sp_rename: "Caution: Changing any part of an
object name could break scripts and stored procedures. The COLUMN was
renamed to 'brap'."?
You should be able to find most stored procedures that rely on the table by
using sp_depends, but because of deferred name resolution and depending on
the order of your script creation, you might not catch all of them.
Assuming it's not a common word like TO or DATE, you can get the list by
scanning ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES, but this isn't
flawless either.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"John Smith" <js@.no.com> wrote in message
news:e1epiOkeEHA.2532@.TK2MSFTNGP09.phx.gbl...
> Hey folks,
> I have alot of column names in a database that I'd like to change however
> there are alot of Stored Procedures that reference the existing names. Is
> there a way to make SQL Server update my Stored Procedures whenever I
change
> a table's column name?
> Thanks!
>|||for a 100% guarentee to pick up any defects due to the
result of an object rename, build a database from the
source code of the database. Due to problems with
sp_depends and the flaws with the information_schema
views, these are no guarentee that you have highlighted
all your potential problems. If you do a build using your
source code, then all errors will show. Of course
building has it's problems as your build order my be an
issue. These are some of the problems that our software
addresses DB Ghost. It can build a database from source
code and it takes care of all dependancies - flagging up
errors within your source code. This is change management
software and answers the question, What happens if I make
a change?
If your interested why not read our white paper on the
subject
http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Ap
proach_To_Do_Change_Mgt.pdf
DB Ghost - the only database change management software
for SQL Server on the market today.
>--Original Message--
>Hey folks,
>I have alot of column names in a database that I'd like
to change however
>there are alot of Stored Procedures that reference the
existing names. Is
>there a way to make SQL Server update my Stored
Procedures whenever I change
>a table's column name?
>Thanks!
>
>.
>|||Will it go so far as telling you that data will fail to be inserted into a
table because the foreign key dependency data is not in the foreign key
table?
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:063a01c47ad3$a4bfd520$a301280a@.phx.gbl...
> for a 100% guarentee to pick up any defects due to the
> result of an object rename, build a database from the
> source code of the database. Due to problems with
> sp_depends and the flaws with the information_schema
> views, these are no guarentee that you have highlighted
> all your potential problems. If you do a build using your
> source code, then all errors will show. Of course
> building has it's problems as your build order my be an
> issue. These are some of the problems that our software
> addresses DB Ghost. It can build a database from source
> code and it takes care of all dependancies - flagging up
> errors within your source code. This is change management
> software and answers the question, What happens if I make
> a change?
> If your interested why not read our white paper on the
> subject
> http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Ap
> proach_To_Do_Change_Mgt.pdf
> DB Ghost - the only database change management software
> for SQL Server on the market today.
> >--Original Message--
> >Hey folks,
> >
> >I have alot of column names in a database that I'd like
> to change however
> >there are alot of Stored Procedures that reference the
> existing names. Is
> >there a way to make SQL Server update my Stored
> Procedures whenever I change
> >a table's column name?
> >
> >Thanks!
> >
> >
> >.
> >|||yes
>--Original Message--
>Will it go so far as telling you that data will fail to
be inserted into a
>table because the foreign key dependency data is not in
the foreign key
>table?
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:063a01c47ad3$a4bfd520$a301280a@.phx.gbl...
>> for a 100% guarentee to pick up any defects due to the
>> result of an object rename, build a database from the
>> source code of the database. Due to problems with
>> sp_depends and the flaws with the information_schema
>> views, these are no guarentee that you have highlighted
>> all your potential problems. If you do a build using
your
>> source code, then all errors will show. Of course
>> building has it's problems as your build order my be an
>> issue. These are some of the problems that our software
>> addresses DB Ghost. It can build a database from source
>> code and it takes care of all dependancies - flagging
up
>> errors within your source code. This is change
management
>> software and answers the question, What happens if I
make
>> a change?
>> If your interested why not read our white paper on the
>> subject
http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Ap
>> proach_To_Do_Change_Mgt.pdf
>> DB Ghost - the only database change management software
>> for SQL Server on the market today.
>> >--Original Message--
>> >Hey folks,
>> >
>> >I have alot of column names in a database that I'd
like
>> to change however
>> >there are alot of Stored Procedures that reference the
>> existing names. Is
>> >there a way to make SQL Server update my Stored
>> Procedures whenever I change
>> >a table's column name?
>> >
>> >Thanks!
>> >
>> >
>> >.
>> >
>
>.
>sql

Changing Column Names

Hey folks,
I have alot of column names in a database that I'd like to change however
there are alot of Stored Procedures that reference the existing names. Is
there a way to make SQL Server update my Stored Procedures whenever I change
a table's column name?
Thanks!
No, SQL Server will not rewrite your code for you ... why do you think you
can't suppress the warning from sp_rename: "Caution: Changing any part of an
object name could break scripts and stored procedures. The COLUMN was
renamed to 'brap'."?
You should be able to find most stored procedures that rely on the table by
using sp_depends, but because of deferred name resolution and depending on
the order of your script creation, you might not catch all of them.
Assuming it's not a common word like TO or DATE, you can get the list by
scanning ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES, but this isn't
flawless either.
http://www.aspfaq.com/
(Reverse address to reply.)
"John Smith" <js@.no.com> wrote in message
news:e1epiOkeEHA.2532@.TK2MSFTNGP09.phx.gbl...
> Hey folks,
> I have alot of column names in a database that I'd like to change however
> there are alot of Stored Procedures that reference the existing names. Is
> there a way to make SQL Server update my Stored Procedures whenever I
change
> a table's column name?
> Thanks!
>

Changing Column Names

for a 100% guarentee to pick up any defects due to the
result of an object rename, build a database from the
source code of the database. Due to problems with
sp_depends and the flaws with the information_schema
views, these are no guarentee that you have highlighted
all your potential problems. If you do a build using your
source code, then all errors will show. Of course
building has it's problems as your build order my be an
issue. These are some of the problems that our software
addresses DB Ghost. It can build a database from source
code and it takes care of all dependancies - flagging up
errors within your source code. This is change management
software and answers the question, What happens if I make
a change?
If your interested why not read our white paper on the
subject
http://www.innovartis.co.uk/pdf/Inno...n_Automated_Ap
proach_To_Do_Change_Mgt.pdf
DB Ghost - the only database change management software
for SQL Server on the market today.

>--Original Message--
>Hey folks,
>I have alot of column names in a database that I'd like
to change however
>there are alot of Stored Procedures that reference the
existing names. Is
>there a way to make SQL Server update my Stored
Procedures whenever I change
>a table's column name?
>Thanks!
>
>.
>
Will it go so far as telling you that data will fail to be inserted into a
table because the foreign key dependency data is not in the foreign key
table?
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:063a01c47ad3$a4bfd520$a301280a@.phx.gbl...[vbcol=seagreen]
> for a 100% guarentee to pick up any defects due to the
> result of an object rename, build a database from the
> source code of the database. Due to problems with
> sp_depends and the flaws with the information_schema
> views, these are no guarentee that you have highlighted
> all your potential problems. If you do a build using your
> source code, then all errors will show. Of course
> building has it's problems as your build order my be an
> issue. These are some of the problems that our software
> addresses DB Ghost. It can build a database from source
> code and it takes care of all dependancies - flagging up
> errors within your source code. This is change management
> software and answers the question, What happens if I make
> a change?
> If your interested why not read our white paper on the
> subject
> http://www.innovartis.co.uk/pdf/Inno...n_Automated_Ap
> proach_To_Do_Change_Mgt.pdf
> DB Ghost - the only database change management software
> for SQL Server on the market today.
> to change however
> existing names. Is
> Procedures whenever I change
|||yes

>--Original Message--
>Will it go so far as telling you that data will fail to
be inserted into a
>table because the foreign key dependency data is not in
the foreign key
>table?
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:063a01c47ad3$a4bfd520$a301280a@.phx.gbl...
your[vbcol=seagreen]
up[vbcol=seagreen]
management[vbcol=seagreen]
make[vbcol=seagreen]
http://www.innovartis.co.uk/pdf/Inno...n_Automated_Ap[vbcol=seagreen]
like
>
>.
>

Changing Character Case

Does anyone have a solution to changing the case of characters in VARCHAR fields? I have several vendor names that have historically all been capitalized, and I would like to change them to Upper and Lower Case (as custom). For example:

WIDGET, INC would become Widget, Inc
ACME LTD would become Acme LTD
JOHNSON & JOHNSON would become Johnson & Johnson

I don' t mind if I have a stray character that inadvertantly gets changed to the wrong case... I can manage to the exceptions. I'm looking at roughly 11,000 records to change.Hello,

that depends on the database that you use:

for Oracle and MySQL and MSSQL : UPDATE table SET company = UPPER(company);

If you use another database, please let us know ...

Hope that helps ?

Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Originally posted by acg_ray
Does anyone have a solution to changing the case of characters in VARCHAR fields? I have several vendor names that have historically all been capitalized, and I would like to change them to Upper and Lower Case (as custom). For example:

WIDGET, INC would become Widget, Inc
ACME LTD would become Acme LTD
JOHNSON & JOHNSON would become Johnson & Johnson

I don' t mind if I have a stray character that inadvertantly gets changed to the wrong case... I can manage to the exceptions. I'm looking at roughly 11,000 records to change.
In Oracle, use the INITCAP function. However, this will convert "LTD" to "Ltd" in your example above.|||UPPER would set all characters to Upper Case, would it not? (as LOWER would set all characters to Lower Case).

I'm looking for the ability to set the first characters of strings to Upper, making rest lower, similar to the INITCAP function in Oracle.

I'm guessing I could use a combination of UPPER, LOWER, and LEFT, which would get me to most of the changes, but that would leave me with lower case letters starting the second or third words in a name.|||Originally posted by acg_ray
UPPER would set all characters to Upper Case, would it not? (as LOWER would set all characters to Lower Case).

I'm looking for the ability to set the first characters of strings to Upper, making rest lower, similar to the INITCAP function in Oracle.

I'm guessing I could use a combination of UPPER, LOWER, and LEFT, which would get me to most of the changes, but that would leave me with lower case letters starting the second or third words in a name.
I just took a look at some SQL Server documentation, and see that there seems to be no equivalent of INITCAP. I guess you could write your own function, using UPPER, LOWER, SUBSTRING and CHARINDEX.
You would use CHARINDEX to find the spaces, then UPPER the character following each space.|||Thanks. That's where I figured this was heading. That's a good direction, and I should be able to proceed from here. I appreciate the info.|||I was able to get this to work (for the most part) with a combination of LEFT, RIGHT, SUBSTRING, LEN, CHARINDEX, UPPER, and LOWER commands. So it's not a bad tool for me to have on hand.

I discovered as an afternote, that if I exported my results to Excel, I could use a command Proper(field) that would automatically do this for me. I could have then brought the results back into MSSQL. Really, a much easier process, since my data sets are small enough to be handled by Excel (15,000 rows).sql

Changing 100 table object owner names at once

Is there any way I can change the object owner for over
100 tables in a single data base at once in SQL 7 without
using the sp on each table?
Well you can do an update on sysobjects but if you look at the sp it does a lot of checking and clearing up.
Better to code a loop to execute it on all the tables necessary.
"Smith" wrote:

> Is there any way I can change the object owner for over
> 100 tables in a single data base at once in SQL 7 without
> using the sp on each table?
>
sql

Change the displayed name in the Series Group for a chart

Hi,
I created a chart in sql reporting services. I added a series group to it.
Now what i want to do is to dispaly different series names when the chart is
viewed . For example, in my database, I have Region code as numbers. I have
used this Region Code as the Series group. What i want to do is, ...
if (RegionCode ==01
{
//the series group should display North
}
Similarly for other region codes. Is it possible? & how?
Thanks
pmudYes this is possible through the Label expression of a chart series
grouping.
* right-click on the chart and open the chart properties dialog
* go to the Data tab and edit the series grouping
* the grouping dialog has a grouping expression and a label expression
below. If no label expression is set, it will take the group expression
value as label by default.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:13D0A129-DEAA-4C05-9736-04E3104A9935@.microsoft.com...
> Hi,
> I created a chart in sql reporting services. I added a series group to it.
> Now what i want to do is to dispaly different series names when the chart
> is
> viewed . For example, in my database, I have Region code as numbers. I
> have
> used this Region Code as the Series group. What i want to do is, ...
> if (RegionCode ==01
> {
> //the series group should display North
> }
> Similarly for other region codes. Is it possible? & how?
> Thanks
> pmud

Change node names and move cluster to new domain

I have an active/passive SQL 2005 cluster consisting of 2 nodes. My bosses
want me to rename both nodes and also move the cluster to a new domain. How
would I do that ?.
Regards
Tony Dawson
There is no easy way to do this.
http://support.microsoft.com/kb/915846/en-us
will get you through the domain change. To change the node names, you will
have to evict, change, and readd each node.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Tony Dawson" <noone@.nowhere.org> wrote in message
news:0E3AECC8-4C34-4BD5-BE89-71D34D023A11@.microsoft.com...
>I have an active/passive SQL 2005 cluster consisting of 2 nodes. My bosses
>want me to rename both nodes and also move the cluster to a new domain. How
>would I do that ?.
> Regards
> Tony Dawson
|||You might want to ask yourself if the reasons for doing this are strong
enough to outweigh the risk and efford of this excercise.
Are there any technical reasons why you want to do this ?
Oh, and take a backup... and test the backup before you begin.
But sure that goes for every maintenance you do.
rgds,
Edwin.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23yjCbRkIIHA.5208@.TK2MSFTNGP04.phx.gbl...
> There is no easy way to do this.
> http://support.microsoft.com/kb/915846/en-us
> will get you through the domain change. To change the node names, you
will[vbcol=seagreen]
> have to evict, change, and readd each node.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Tony Dawson" <noone@.nowhere.org> wrote in message
> news:0E3AECC8-4C34-4BD5-BE89-71D34D023A11@.microsoft.com...
bosses[vbcol=seagreen]
How
>
|||Geoff, that KB article handles the domain *group* change but not changing
the domain. I don't think you can move a cluster to another domain.
As for renaming nodes, the only way to do that is to:
1) evict one node
2) rename the node
3) bring the node back into the cluster
4) re-apply the binaries to the node
5) repeat for all nodes
This is all a major PITA. I think there has to be very compelling reason to
change domains or rename nodes within a server.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23yjCbRkIIHA.5208@.TK2MSFTNGP04.phx.gbl...
There is no easy way to do this.
http://support.microsoft.com/kb/915846/en-us
will get you through the domain change. To change the node names, you will
have to evict, change, and readd each node.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Tony Dawson" <noone@.nowhere.org> wrote in message
news:0E3AECC8-4C34-4BD5-BE89-71D34D023A11@.microsoft.com...
>I have an active/passive SQL 2005 cluster consisting of 2 nodes. My bosses
>want me to rename both nodes and also move the cluster to a new domain. How
>would I do that ?.
> Regards
> Tony Dawson

Change merge filter

I like to change a filter of a article. I'm using sp_changemergefilter but I
need the name of the filter.
I have the names of the filters with sp_helpmergefilter but only give me the
join filters.
I like to know if sp_changemergefilter only works with join filters?
And how can I change a filter that is not join filter?
Thanks for your help
David Barquero
I think you want to use sp_changemergearticle, something like this:
exec sp_changemergearticle @.publication = 'Tier1', @.article = 'MergeTable',
@.force_invalidate_snapshot = 1, @.force_reinit_subscription = 1, @.property =
'subset_filterclause', @.value = 'locationID=1'
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"David Barquero" <davbarquero@.hotmail.com> wrote in message
news:ujr53NP%23EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I like to change a filter of a article. I'm using sp_changemergefilter but
I
> need the name of the filter.
> I have the names of the filters with sp_helpmergefilter but only give me
the
> join filters.
> I like to know if sp_changemergefilter only works with join filters?
> And how can I change a filter that is not join filter?
>
> Thanks for your help
> David Barquero
>
|||Hilary Thanks for your help
I try to do it like you say me, but when I'm runing the SP it's give me
this error
Server: Msg 21416, Level 16, State 1, Procedure sp_changemergearticle, Line
272
Property 'subset_filterclause' of article 'TomaFisica' cannot be changed.
Why I Can't change the filterclause?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23ixyH1X%23EHA.2032@.tk2msftngp13.phx.gbl...
>I think you want to use sp_changemergearticle, something like this:
> exec sp_changemergearticle @.publication = 'Tier1', @.article =
> 'MergeTable',
> @.force_invalidate_snapshot = 1, @.force_reinit_subscription = 1, @.property
> =
> 'subset_filterclause', @.value = 'locationID=1'
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "David Barquero" <davbarquero@.hotmail.com> wrote in message
> news:ujr53NP%23EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I
> the
>