Rename Columns

I need some help with renaming about 115 columns
Here is what I have thus far
EXEC sp_rename
'Copy_AFS.Col015','INTEREST-PAYMENT-CODE',
'Copy_AFS.Col016','INTEREST-COMPOUND-FREQUENCY',
'Copy_AFS.Col017','INTEREST-PAYMENT-FREQUENCY',
'Copy_AFS.Col018','NEXT-INTEREST-PAYMENT-DATE',
'Copy_AFS.Col019','ORIGINAL-TRANS-PURCH-DATE',
'Copy_AFS.Col020','ORIGINAL-BOOK-VALUE',
'Copy_AFS.Col021','PREM-DISC-AT-TRANS-PURCH',
'Copy_AFS.Col022','PREPAY-PERCENT-PER-MONTH',
'Copy_AFS.Col023','LOWEST-LIFETIME-RATE',
'Copy_AFS.Col024','HIGHEST-LIFETIME-RATE',
'Copy_AFS.Col025','MAXIMUM-RATE-CHANGE',
'Copy_AFS.Col026','REPRICING-SPREAD-VALUE',
'Copy_AFS.Col027','LAST-REPRICING-DATE',
'Copy_AFS.Col028','AMORTIZATION-DATE',
'Copy_AFS.Col029','COTERM-RATE',
'Copy_AFS.Col030','COTERM-INDEX',
'Copy_AFS.Col031','AVERAGE-BALANCE',
'Copy_AFS.Col032','TRANSACTION-STATUS-CODE',
'Copy_AFS.Col033','REPRICING-DRIVE-RATE',
'Copy_AFS.Col034','INTEREST-INCOME-MTD',
'Copy_AFS.Col035','INTEREST-INCOME-YTD',
'Copy_AFS.Col036','PROCESSING-DATE',
'Copy_AFS.Col037','ACCT-NUM',
'Copy_AFS.Col038','BANK-NUM',
'Copy_AFS.Col039','CORP-NUM',
'Copy_AFS.Col040','BRANCH-NUM',
'Copy_AFS.Col041','CREDIT-RISK-RATING-CODE',
'Copy_AFS.Col042','COMMERCIAL-RETAIL-FLAG',
'Copy_AFS.Col043','CUSTOMER-NUM',
'Copy_AFS.Col044','COUNTERPARTY-INFO',
'Copy_AFS.Col045','GL-ACCT-NUM-II-PRIN',
'Copy_AFS.Col046','GL-ACCT-NUM-II-IE',
'Copy_AFS.Col047','GL-ACCT-NUM-ACCRUED-INT',
'Copy_AFS.Col048','PRODUCT-CODE',
'Copy_AFS.Col049','SERVICED-LOAN-INDICATOR',
'Copy_AFS.Col050','Special Subsystem FTP Code',
'Copy_AFS.Col051','RESP-CNTR-NUM-II-PRIN',
'Copy_AFS.Col052','RESP-CNTR-NUM-II-IE',
'Copy_AFS.Col053','RESP-CNTR-NUM-ACCRUED-INT',
'Copy_AFS.Col054','BRANCH-RC',
'Copy_AFS.Col055','SIC-CODE',
'Copy_AFS.Col056','SUBSYSID',
'Copy_AFS.Col057','ACCRUED-INT-BAL',
'Copy_AFS.Col058','AVERAGE-COTERM-RATE',
'Copy_AFS.Col059','FTP-CHARGE-CREDIT',
'Copy_AFS.Col060','FTP-LIQUIDITY-CHARGE',
'Copy_AFS.Col061','VARIABLE-COST',
'Copy_AFS.Col062','FIXED-COST',
'Copy_AFS.Col063','OVERHEAD-COST',
'Copy_AFS.Col064','ORIG-TERM',
'Copy_AFS.Col065','REMAINING-TERM',
'Copy_AFS.Col066','AMT-OVERDUE-30',
'Copy_AFS.Col067','AMT-OVERDUE-60',
'Copy_AFS.Col068','AMT-OVERDUE-90',
'Copy_AFS.Col069','AMT-OVERDUE-GT-90',
'Copy_AFS.Col070','PAST-DUE-DAYS',
'Copy_AFS.Col071','FUNDING-SOURCE',
'Copy_AFS.Col072','Fill-1',
'Copy_AFS.Col073','GL-FUNDING-ROLLUP',
'Copy_AFS.Col074','GL-FUNDING-ROLLUP-EXCEPTION',
'Copy_AFS.Col075','FTP-GL-ACCOUNT',
'Copy_AFS.Col076','FTP-LIQUIDITY-ACCOUNT',
'Copy_AFS.Col077','FUNDING-LOOKBACK',
'Copy_AFS.Col078','Filler',
'Copy_AFS.Col079','CALCULATED-COTERM-RATE',
'Copy_AFS.Col080','LIQUIDITY-PREMIUM',
'Copy_AFS.Col081','PREMIUM-TWO',
'Copy_AFS.Col082','PREMIUM-THREE',
'Copy_AFS.Col083','PREMIUM-FOUR',
'Copy_AFS.Col084','FTP-LASTREPRICINGDATE',
'Copy_AFS.Col085','FTP-ORIGINAL-BALANCE',
'Copy_AFS.Col086','FTP-CURRENT-YLD',
'Copy_AFS.Col087','PRODUCT',
'Copy_AFS.Col088','COUNTRY-OF-DOMICILE',
'Copy_AFS.Col089','ACCOUNT-STATUS',
'Copy_AFS.Col090','CURRENT-PAYOFF-AMOUNT',
'Copy_AFS.Col091','OBLIGATION TYPE',
'Copy_AFS.Col092','PROCESSING-TYPE (MC038)',
'Copy_AFS.Col093','INT-HDR-EFFECTIVE-FROM-DATE',
'Copy_AFS.Col094','INT-HDR-EFFECTIVE-TO-DATE',
'Copy_AFS.Col095','INTEREST-PRIME-NUMBER',
'Copy_AFS.Col096','PRIME-EFF-FROM-DATE',
'Copy_AFS.Col097','EFF-THROUGH-DATE (RJ330)',
'Copy_AFS.Col098','TICKLER-TYPE',
'Copy_AFS.Col099','TICKLER-START-DATE',
'Copy_AFS.Col100','TICKLER-EXPIRY-DATE',
'Copy_AFS.Col101','TICKLER-DESCRIPTION',
'Copy_AFS.Col102','PEHERNTY (PE908)',
'Copy_AFS.Col103','TAKEDOWN-OBLIGATION',
'Copy_AFS.Col104','TAKEDOWN-OBLIGOR',
'Copy_AFS.Col105','CHARGE-CD',
'Copy_AFS.Col106','AGGREGATE-BAL-PRINCIPAL',
'Copy_AFS.Col107','LAST-RENEWAL-DATE',
'Copy_AFS.Col108','ACCRUAL-METHOD (PE906)',
'Copy_AFS.Col109','AMORT-CODE (RJ440)',
'Copy_AFS.Col110','INT-HDR-EARNINGS-TYPE',
'Copy_AFS.Col111','AMORT-DATE-CALC',
'Copy_AFS.Col112','Filler2',
'Copy_AFS.Col113','BRANCH-ID',
'Copy_AFS.Col114','OFFICER-CODE',
'Copy_AFS.Col115','FILLER2';
But I am getting the following error
"Procedure or function sp_rename has too many arguments specified."
Please HelpHi, Chris
The error message is correct: there are way too many arguments... Also,
it's not a good idea to use hypens in column names; you should replace
them with underlines. You should use something like this:
EXEC sp_rename 'Copy_AFS.Col015','INTEREST_PAYMENT_CODE'
EXEC sp_rename 'Copy_AFS.Col016','INTEREST_COMPOUND_FREQUENCY'
[...]
Razvan|||sp_rename renames one column at a time and you should specify that you are
renaming a column. So you need
EXEC sp_rename
'Copy_AFS.Col015','INTEREST-PAYMENT-CODE','COLUMN'
EXEC sp_rename
'Copy_AFS.Col016','INTEREST-COMPOUND-FREQUENCY','COLUMN'
EXEC sp_rename
'Copy_AFS.Col017','INTEREST-PAYMENT-FREQUENCY','COLUMN'
etc
Tom
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:55EBC93B-282D-4361-8DDB-DA4C02862F5E@.microsoft.com...
>I need some help with renaming about 115 columns
> Here is what I have thus far
> EXEC sp_rename
> 'Copy_AFS.Col015','INTEREST-PAYMENT-CODE',
> 'Copy_AFS.Col016','INTEREST-COMPOUND-FREQUENCY',
> 'Copy_AFS.Col017','INTEREST-PAYMENT-FREQUENCY',
> 'Copy_AFS.Col018','NEXT-INTEREST-PAYMENT-DATE',
> 'Copy_AFS.Col019','ORIGINAL-TRANS-PURCH-DATE',
> 'Copy_AFS.Col020','ORIGINAL-BOOK-VALUE',
> 'Copy_AFS.Col021','PREM-DISC-AT-TRANS-PURCH',
> 'Copy_AFS.Col022','PREPAY-PERCENT-PER-MONTH',
> 'Copy_AFS.Col023','LOWEST-LIFETIME-RATE',
> 'Copy_AFS.Col024','HIGHEST-LIFETIME-RATE',
> 'Copy_AFS.Col025','MAXIMUM-RATE-CHANGE',
> 'Copy_AFS.Col026','REPRICING-SPREAD-VALUE',
> 'Copy_AFS.Col027','LAST-REPRICING-DATE',
> 'Copy_AFS.Col028','AMORTIZATION-DATE',
> 'Copy_AFS.Col029','COTERM-RATE',
> 'Copy_AFS.Col030','COTERM-INDEX',
> 'Copy_AFS.Col031','AVERAGE-BALANCE',
> 'Copy_AFS.Col032','TRANSACTION-STATUS-CODE',
> 'Copy_AFS.Col033','REPRICING-DRIVE-RATE',
> 'Copy_AFS.Col034','INTEREST-INCOME-MTD',
> 'Copy_AFS.Col035','INTEREST-INCOME-YTD',
> 'Copy_AFS.Col036','PROCESSING-DATE',
> 'Copy_AFS.Col037','ACCT-NUM',
> 'Copy_AFS.Col038','BANK-NUM',
> 'Copy_AFS.Col039','CORP-NUM',
> 'Copy_AFS.Col040','BRANCH-NUM',
> 'Copy_AFS.Col041','CREDIT-RISK-RATING-CODE',
> 'Copy_AFS.Col042','COMMERCIAL-RETAIL-FLAG',
> 'Copy_AFS.Col043','CUSTOMER-NUM',
> 'Copy_AFS.Col044','COUNTERPARTY-INFO',
> 'Copy_AFS.Col045','GL-ACCT-NUM-II-PRIN',
> 'Copy_AFS.Col046','GL-ACCT-NUM-II-IE',
> 'Copy_AFS.Col047','GL-ACCT-NUM-ACCRUED-INT',
> 'Copy_AFS.Col048','PRODUCT-CODE',
> 'Copy_AFS.Col049','SERVICED-LOAN-INDICATOR',
> 'Copy_AFS.Col050','Special Subsystem FTP Code',
> 'Copy_AFS.Col051','RESP-CNTR-NUM-II-PRIN',
> 'Copy_AFS.Col052','RESP-CNTR-NUM-II-IE',
> 'Copy_AFS.Col053','RESP-CNTR-NUM-ACCRUED-INT',
> 'Copy_AFS.Col054','BRANCH-RC',
> 'Copy_AFS.Col055','SIC-CODE',
> 'Copy_AFS.Col056','SUBSYSID',
> 'Copy_AFS.Col057','ACCRUED-INT-BAL',
> 'Copy_AFS.Col058','AVERAGE-COTERM-RATE',
> 'Copy_AFS.Col059','FTP-CHARGE-CREDIT',
> 'Copy_AFS.Col060','FTP-LIQUIDITY-CHARGE',
> 'Copy_AFS.Col061','VARIABLE-COST',
> 'Copy_AFS.Col062','FIXED-COST',
> 'Copy_AFS.Col063','OVERHEAD-COST',
> 'Copy_AFS.Col064','ORIG-TERM',
> 'Copy_AFS.Col065','REMAINING-TERM',
> 'Copy_AFS.Col066','AMT-OVERDUE-30',
> 'Copy_AFS.Col067','AMT-OVERDUE-60',
> 'Copy_AFS.Col068','AMT-OVERDUE-90',
> 'Copy_AFS.Col069','AMT-OVERDUE-GT-90',
> 'Copy_AFS.Col070','PAST-DUE-DAYS',
> 'Copy_AFS.Col071','FUNDING-SOURCE',
> 'Copy_AFS.Col072','Fill-1',
> 'Copy_AFS.Col073','GL-FUNDING-ROLLUP',
> 'Copy_AFS.Col074','GL-FUNDING-ROLLUP-EXCEPTION',
> 'Copy_AFS.Col075','FTP-GL-ACCOUNT',
> 'Copy_AFS.Col076','FTP-LIQUIDITY-ACCOUNT',
> 'Copy_AFS.Col077','FUNDING-LOOKBACK',
> 'Copy_AFS.Col078','Filler',
> 'Copy_AFS.Col079','CALCULATED-COTERM-RATE',
> 'Copy_AFS.Col080','LIQUIDITY-PREMIUM',
> 'Copy_AFS.Col081','PREMIUM-TWO',
> 'Copy_AFS.Col082','PREMIUM-THREE',
> 'Copy_AFS.Col083','PREMIUM-FOUR',
> 'Copy_AFS.Col084','FTP-LASTREPRICINGDATE',
> 'Copy_AFS.Col085','FTP-ORIGINAL-BALANCE',
> 'Copy_AFS.Col086','FTP-CURRENT-YLD',
> 'Copy_AFS.Col087','PRODUCT',
> 'Copy_AFS.Col088','COUNTRY-OF-DOMICILE',
> 'Copy_AFS.Col089','ACCOUNT-STATUS',
> 'Copy_AFS.Col090','CURRENT-PAYOFF-AMOUNT',
> 'Copy_AFS.Col091','OBLIGATION TYPE',
> 'Copy_AFS.Col092','PROCESSING-TYPE (MC038)',
> 'Copy_AFS.Col093','INT-HDR-EFFECTIVE-FROM-DATE',
> 'Copy_AFS.Col094','INT-HDR-EFFECTIVE-TO-DATE',
> 'Copy_AFS.Col095','INTEREST-PRIME-NUMBER',
> 'Copy_AFS.Col096','PRIME-EFF-FROM-DATE',
> 'Copy_AFS.Col097','EFF-THROUGH-DATE (RJ330)',
> 'Copy_AFS.Col098','TICKLER-TYPE',
> 'Copy_AFS.Col099','TICKLER-START-DATE',
> 'Copy_AFS.Col100','TICKLER-EXPIRY-DATE',
> 'Copy_AFS.Col101','TICKLER-DESCRIPTION',
> 'Copy_AFS.Col102','PEHERNTY (PE908)',
> 'Copy_AFS.Col103','TAKEDOWN-OBLIGATION',
> 'Copy_AFS.Col104','TAKEDOWN-OBLIGOR',
> 'Copy_AFS.Col105','CHARGE-CD',
> 'Copy_AFS.Col106','AGGREGATE-BAL-PRINCIPAL',
> 'Copy_AFS.Col107','LAST-RENEWAL-DATE',
> 'Copy_AFS.Col108','ACCRUAL-METHOD (PE906)',
> 'Copy_AFS.Col109','AMORT-CODE (RJ440)',
> 'Copy_AFS.Col110','INT-HDR-EARNINGS-TYPE',
> 'Copy_AFS.Col111','AMORT-DATE-CALC',
> 'Copy_AFS.Col112','Filler2',
> 'Copy_AFS.Col113','BRANCH-ID',
> 'Copy_AFS.Col114','OFFICER-CODE',
> 'Copy_AFS.Col115','FILLER2';
> But I am getting the following error
> "Procedure or function sp_rename has too many arguments specified."
> Please Help
>|||Beware of renaming in database production environment.
sp_rename make a complex SQL Script passing through a temp table.
This can make an extremly high volume of data in log file wich can go
down your server.
Do it in a single temp table if all the columns are in the same table !
A +
Chris a écrit :
> I need some help with renaming about 115 columns
> Here is what I have thus far
> EXEC sp_rename
> 'Copy_AFS.Col015','INTEREST-PAYMENT-CODE',
> 'Copy_AFS.Col016','INTEREST-COMPOUND-FREQUENCY',
> 'Copy_AFS.Col017','INTEREST-PAYMENT-FREQUENCY',
> 'Copy_AFS.Col018','NEXT-INTEREST-PAYMENT-DATE',
> 'Copy_AFS.Col019','ORIGINAL-TRANS-PURCH-DATE',
> 'Copy_AFS.Col020','ORIGINAL-BOOK-VALUE',
> 'Copy_AFS.Col021','PREM-DISC-AT-TRANS-PURCH',
> 'Copy_AFS.Col022','PREPAY-PERCENT-PER-MONTH',
> 'Copy_AFS.Col023','LOWEST-LIFETIME-RATE',
> 'Copy_AFS.Col024','HIGHEST-LIFETIME-RATE',
> 'Copy_AFS.Col025','MAXIMUM-RATE-CHANGE',
> 'Copy_AFS.Col026','REPRICING-SPREAD-VALUE',
> 'Copy_AFS.Col027','LAST-REPRICING-DATE',
> 'Copy_AFS.Col028','AMORTIZATION-DATE',
> 'Copy_AFS.Col029','COTERM-RATE',
> 'Copy_AFS.Col030','COTERM-INDEX',
> 'Copy_AFS.Col031','AVERAGE-BALANCE',
> 'Copy_AFS.Col032','TRANSACTION-STATUS-CODE',
> 'Copy_AFS.Col033','REPRICING-DRIVE-RATE',
> 'Copy_AFS.Col034','INTEREST-INCOME-MTD',
> 'Copy_AFS.Col035','INTEREST-INCOME-YTD',
> 'Copy_AFS.Col036','PROCESSING-DATE',
> 'Copy_AFS.Col037','ACCT-NUM',
> 'Copy_AFS.Col038','BANK-NUM',
> 'Copy_AFS.Col039','CORP-NUM',
> 'Copy_AFS.Col040','BRANCH-NUM',
> 'Copy_AFS.Col041','CREDIT-RISK-RATING-CODE',
> 'Copy_AFS.Col042','COMMERCIAL-RETAIL-FLAG',
> 'Copy_AFS.Col043','CUSTOMER-NUM',
> 'Copy_AFS.Col044','COUNTERPARTY-INFO',
> 'Copy_AFS.Col045','GL-ACCT-NUM-II-PRIN',
> 'Copy_AFS.Col046','GL-ACCT-NUM-II-IE',
> 'Copy_AFS.Col047','GL-ACCT-NUM-ACCRUED-INT',
> 'Copy_AFS.Col048','PRODUCT-CODE',
> 'Copy_AFS.Col049','SERVICED-LOAN-INDICATOR',
> 'Copy_AFS.Col050','Special Subsystem FTP Code',
> 'Copy_AFS.Col051','RESP-CNTR-NUM-II-PRIN',
> 'Copy_AFS.Col052','RESP-CNTR-NUM-II-IE',
> 'Copy_AFS.Col053','RESP-CNTR-NUM-ACCRUED-INT',
> 'Copy_AFS.Col054','BRANCH-RC',
> 'Copy_AFS.Col055','SIC-CODE',
> 'Copy_AFS.Col056','SUBSYSID',
> 'Copy_AFS.Col057','ACCRUED-INT-BAL',
> 'Copy_AFS.Col058','AVERAGE-COTERM-RATE',
> 'Copy_AFS.Col059','FTP-CHARGE-CREDIT',
> 'Copy_AFS.Col060','FTP-LIQUIDITY-CHARGE',
> 'Copy_AFS.Col061','VARIABLE-COST',
> 'Copy_AFS.Col062','FIXED-COST',
> 'Copy_AFS.Col063','OVERHEAD-COST',
> 'Copy_AFS.Col064','ORIG-TERM',
> 'Copy_AFS.Col065','REMAINING-TERM',
> 'Copy_AFS.Col066','AMT-OVERDUE-30',
> 'Copy_AFS.Col067','AMT-OVERDUE-60',
> 'Copy_AFS.Col068','AMT-OVERDUE-90',
> 'Copy_AFS.Col069','AMT-OVERDUE-GT-90',
> 'Copy_AFS.Col070','PAST-DUE-DAYS',
> 'Copy_AFS.Col071','FUNDING-SOURCE',
> 'Copy_AFS.Col072','Fill-1',
> 'Copy_AFS.Col073','GL-FUNDING-ROLLUP',
> 'Copy_AFS.Col074','GL-FUNDING-ROLLUP-EXCEPTION',
> 'Copy_AFS.Col075','FTP-GL-ACCOUNT',
> 'Copy_AFS.Col076','FTP-LIQUIDITY-ACCOUNT',
> 'Copy_AFS.Col077','FUNDING-LOOKBACK',
> 'Copy_AFS.Col078','Filler',
> 'Copy_AFS.Col079','CALCULATED-COTERM-RATE',
> 'Copy_AFS.Col080','LIQUIDITY-PREMIUM',
> 'Copy_AFS.Col081','PREMIUM-TWO',
> 'Copy_AFS.Col082','PREMIUM-THREE',
> 'Copy_AFS.Col083','PREMIUM-FOUR',
> 'Copy_AFS.Col084','FTP-LASTREPRICINGDATE',
> 'Copy_AFS.Col085','FTP-ORIGINAL-BALANCE',
> 'Copy_AFS.Col086','FTP-CURRENT-YLD',
> 'Copy_AFS.Col087','PRODUCT',
> 'Copy_AFS.Col088','COUNTRY-OF-DOMICILE',
> 'Copy_AFS.Col089','ACCOUNT-STATUS',
> 'Copy_AFS.Col090','CURRENT-PAYOFF-AMOUNT',
> 'Copy_AFS.Col091','OBLIGATION TYPE',
> 'Copy_AFS.Col092','PROCESSING-TYPE (MC038)',
> 'Copy_AFS.Col093','INT-HDR-EFFECTIVE-FROM-DATE',
> 'Copy_AFS.Col094','INT-HDR-EFFECTIVE-TO-DATE',
> 'Copy_AFS.Col095','INTEREST-PRIME-NUMBER',
> 'Copy_AFS.Col096','PRIME-EFF-FROM-DATE',
> 'Copy_AFS.Col097','EFF-THROUGH-DATE (RJ330)',
> 'Copy_AFS.Col098','TICKLER-TYPE',
> 'Copy_AFS.Col099','TICKLER-START-DATE',
> 'Copy_AFS.Col100','TICKLER-EXPIRY-DATE',
> 'Copy_AFS.Col101','TICKLER-DESCRIPTION',
> 'Copy_AFS.Col102','PEHERNTY (PE908)',
> 'Copy_AFS.Col103','TAKEDOWN-OBLIGATION',
> 'Copy_AFS.Col104','TAKEDOWN-OBLIGOR',
> 'Copy_AFS.Col105','CHARGE-CD',
> 'Copy_AFS.Col106','AGGREGATE-BAL-PRINCIPAL',
> 'Copy_AFS.Col107','LAST-RENEWAL-DATE',
> 'Copy_AFS.Col108','ACCRUAL-METHOD (PE906)',
> 'Copy_AFS.Col109','AMORT-CODE (RJ440)',
> 'Copy_AFS.Col110','INT-HDR-EARNINGS-TYPE',
> 'Copy_AFS.Col111','AMORT-DATE-CALC',
> 'Copy_AFS.Col112','Filler2',
> 'Copy_AFS.Col113','BRANCH-ID',
> 'Copy_AFS.Col114','OFFICER-CODE',
> 'Copy_AFS.Col115','FILLER2';
> But I am getting the following error
> "Procedure or function sp_rename has too many arguments specified."
> Please Help
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||> sp_rename make a complex SQL Script passing through a temp table.
> This can make an extremly high volume of data in log file wich can go down your se
rver.
I don't understand what you are saying here. Sp_rename does *not* produce a
large number of log
records. In fact, a test show that the procedure produces less than 10 log r
ecords, regardless of
the number of rows in the table. the procedure only changes a value in one o
f the system tables, so
no need for producing lots of log records.
Run below if you want to verify:
USE master
GO
DROP DATABASE x
GO
CREATE DATABASE x
GO
USE x
GO
ALTER DATABASE x SET RECOVERY FULL
BACKUP DATABASE x TO DISK = 'nul'
CREATE TABLE t(c1 int)
GO
BACKUP LOG x TO DISK = 'nul'
SELECT COUNT(*) FROM ::fn_dblog(NULL, NULL)
EXEC sp_rename 't.c1', 'cX', 'column'
SELECT COUNT(*) FROM ::fn_dblog(NULL, NULL)
GO
--Insert 1000 rows and prove that no more logging:
INSERT INTO t(cX)
SELECT TOP 10000 1 FROM master..sysobjects, master..syscolumns
BACKUP LOG x TO DISK = 'nul'
SELECT COUNT(*) FROM ::fn_dblog(NULL, NULL)
EXEC sp_rename 't.cX', 'cY', 'column'
SELECT COUNT(*) FROM ::fn_dblog(NULL, NULL)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:%233smgTNTGHA.4952@.TK2MSFTNGP09.phx.gbl...
> Beware of renaming in database production environment.
> sp_rename make a complex SQL Script passing through a temp table.
> This can make an extremly high volume of data in log file wich can go down
your server.
> Do it in a single temp table if all the columns are in the same table !
> A +
> Chris a écrit :
>
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQ
L
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************sql