| Author |
Topic |
|
Vijaykumar_Patil
Posting Yak Master
121 Posts |
 Posted - 2007-06-01 : 08:56:10
|
| Hi All,If the column names are changed , is there are a easy way to change the referred columns in the SP's. What I am doing is going into each SP and replace the old name with the new name.Thank you for any help on this.Necessity is the mother of all inventions! |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-01 : 09:00:44
|
| or Generate Script for all SPs and do find and replaceMadhivananFailing to plan is Planning to fail |
 |
|
|
Vijaykumar_Patil
Posting Yak Master
121 Posts |
Posted - 2007-06-01 : 09:02:22
|
| Can generate all the scripts for the SPs at once in a DB?Necessity is the mother of all inventions! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-01 : 09:07:46
|
| Yes. In Enterprise Manager, right click on database->Generate SQL ScriptNow select all procedures and uncheck othersMadhivananFailing to plan is Planning to fail |
|
|
|
Vijaykumar_Patil
Posting Yak Master
121 Posts |
Posted - 2007-06-01 : 09:10:48
|
| Hey thank you Madhivanan. That help.Necessity is the mother of all inventions! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-01 : 11:01:13
|
| FWIW we use unique column names, within the database. (We prefix each column name with a mnemonic which uniquely identifies the table it belongs to). We use the column name for any working variables etc. It is therefore easy, and safe!, to do a global Find & Replace to locate all the occurrences of a column (and indeed any application variables) when it changes - including if we, for example, change the Size / Type of the column.I have also used SED to globally change a unique name across numerous files. I wrote something that would create Regular Expressions for a Find & Replace given a list of Before/After names that needed changing.Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-01 : 11:06:46
|
| <<We prefix each column name with a mnemonic which uniquely identifies the table it belongs to>>So, for Customers table, do you name columns like following?customer_idCustomer_namecustomer_addressetcMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-01 : 11:37:22
|
| Yup, although we would use a prefix that is a bit shorter than "customer_" - e.g. "cust_"we actually use a 2 part prefix:Module - e.g. ACC = AccountsTable - e.g. CUST = Customerso the "prefix" for each column would be "acc_cust_MyField"Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-01 : 12:26:39
|
| Thanks Kristen. I know some people using those conventions. Also some prefer using table_pk_col, table_fk_col, etcMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-01 : 17:05:26
|
| Its the Hungarian Notation bandwagon thingie!Personally I don't feel the need to know, from the Column Name, that it is a PK or FK target.I might like to know that it is String / Date / Int / Float, but having said that I do NOT include anything to indicate that in the column name. But I do for any working variables ...I am quite taken with the whole Hungarian thing (actually, not the "whole" at all, just the bits of it that I rather like!). IF I used it for my column names and wrote this code:WHERE stringSomeColumn = dateOtherColumnit would be immediately obvious that I was mixing data types ...... but as I said, we only do this in our Application code, not in your SQL Column Names.Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-02 : 01:41:58
|
Good  I do think soAlso, I strongly disagree with the conventions followed by some where table names start with tbl_, SPs start with sp_, views start with vw_, etc, to indicate what they are.I always used to ask them what If I create a SP named tbl_mySPWith that conventions tbl_mySP can be easily identified that it is TABLE than SPThanks for your inputs MadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-05 : 04:19:10
|
I liked Jez's column names that I saw somewhere recently, something likeSELECT [select * from] FROM [where] WHERE [join] = 'order by'or somesuch ...  |
|
|
|
Vijaykumar_Patil
Posting Yak Master
121 Posts |
Posted - 2007-06-05 : 04:34:32
|
Kristen...I dont want to be working on a db like that. No ways......... Necessity is the mother of all inventions! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-05 : 07:30:53
|
quote:
Originally posted by Kristen
I liked Jez's column names that I saw somewhere recently, something likeSELECT [select * from] FROM [where] WHERE [join] = 'order by'or somesuch ...
How about this one?SELECT [select] FROM [from] WHERE [where] = 'where'and [delete]='delete' and [null] is not nullMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-05 : 07:34:35
|
| There is a design fault in your database:Why would [null] be NOT NULL? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-05 : 09:07:00
|
| <<Why would [null] be NOT NULL? >>[null] column is defined to have NULL MadhivananFailing to plan is Planning to fail |
|
|
|
Vijaykumar_Patil
Posting Yak Master
121 Posts |
Posted - 2007-06-05 : 09:45:46
|
| Then u will need 2 columns [NULL] and [NOT NULL]Necessity is the mother of all inventions! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-05 : 09:54:52
|
| So you will end up with where [null] is not null and [not null] is null MadhivananFailing to plan is Planning to fail |
|
|
|
Vijaykumar_Patil
Posting Yak Master
121 Posts |
Posted - 2007-06-05 : 10:00:59
|
| That can also be WHERE [NULL] IS NULL AND [NOT NULL] IS NOT NULLNecessity is the mother of all inventions! |
|
|
|
|
Changing SP with the changes in the table