Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
k_cire0426
Yak Posting Veteran
63 Posts |
 Posted - 2010-07-22 : 09:16:03
|
| Hi everyone I am now facing a problem which I don't know specifically what feature of SQL will i use. We have removed some fields on our tables. We update our existing views and procedures for that changes but the problem is we have a client still using that field on their own query or views or procedures. I want to update their views or query to removed the fields that we removed. Is there a way to do that? Replace function is not advisable because of the different kinds of querying like using function on a field. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-22 : 09:53:08
|
| sys.depends table may tell you which of the Client's Sprocs / Views etc. are referencing the deprecated columns.I'm not sure that sys.depends is 100% reliable (it wasn't in SQL 2000, supposed fixed, or better, in SQL 2005, but I have no personal knowledge of whether it ix fixed, or not).You could put columns in your VIEW for the now-deprecated columns that will indicate missing data. Perhaps for VARCHAR columns you could output "Deprecated in v2010" if the original field was big enough; we output a price of 999,999,999 for things that should never show! such that customers don't buy them ... perhaps other placed NULL or large numbers / impossible dates / etc. will show up in the application to help find them.Or, if you leave the columns out of the new VIEW, then the old application should just give an Error if it attempts to show use them. |
 |
|
|
k_cire0426
Yak Posting Veteran
63 Posts |
Posted - 2010-07-22 : 10:40:31
|
| Hello I will give and example in order to understand everyone reading this.We have a table called table_one and we created a view to select some of the fields on that table and the view called "vyu_test"create view vyu_testasselect field1,isnull(field2,0) from table_one group by field1,field2we have removed the field2 on table_one and updated our vyu_test.but then our client customized the vyu_test for their own purpose. it's like the added some field on the view and use new name vyu_customtesthow can we update the vyu_customtest to remove the field2? |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-07-22 : 11:15:19
|
| you want this done programatically or manually? What Kristen says is on the money. Otherwise script it all out and search and remove.If you don't have the passion to help people, you have no passion |
|
|
|
k_cire0426
Yak Posting Veteran
63 Posts |
Posted - 2010-07-22 : 11:31:52
|
| programatically...i did search and remove but did not cater all the scenario on using fields like some of them is using function (isnull(field,0)) and other user is with case statement. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-22 : 11:41:52
|
| Probably no help, but we use unique names for every column in the database - makes it much easier to reliably do Find & Replace type operations. Column name is used (with Suffix/Prefix if necessary) in all temporary variables, and application variables, to further make this possible. |
|
|
|
k_cire0426
Yak Posting Veteran
63 Posts |
Posted - 2010-07-22 : 11:51:56
|
| thanks kristen for your time. Our application is customizable that's why we have that kind of architecture. |
|
|
|
|
|
|
|
|
removed fields on view and procedures