Bulk changes the column name-CodePudding

Table imp_sheet1, the structure is as follows:

Col1 col2 col3 col4 col5 col6 col7 col8
Company the name of the company purchase order line item receiving documents receiving line item last Posting date of receiving the actual receipt date

Above table the column col behavior, increasing in number order, Chinese characters need to be modified as the column, is not necessarily the line 8, there could be 10 lines, 100 lines, to ask how to use the statement bulk changes the column name,

CodePudding user response:

 USE tempdb for 
GO 
IF OBJECT_ID (' t ') IS NOT NULL 
DROP TABLE t 
GO 
CREATE TABLE (t 
Name NVARCHAR (20), 
Gender INT, 
Birthday DATETIME 
) 
GO 
-- -- -- -- -- -- -- -- -- -- to demonstrate above table -- -- -- -- -- -- -- -- -- -- -- -- -- 
SELECT * FROM t 
/* 
Name gender birthday 
*/

- 1. The table variables 
DECLARE @ r TABLE (
Column_id INT, 
[name] NVARCHAR (100) 
) 
- 2. Existing list in the table variable 
INSERT INTO @ r (column_id, [name]) 
The SELECT column_id, [name] FROM sys. The columns WHERE OBJECT_ID=OBJECT_ID (' t '); 

-- 3. Iterate through all the columns, and change the column name 
DECLARE @ I INT, @ iMax INT, @ oldName NVARCHAR (30), @ newName NVARCHAR (30) 
SELECT @ I=1, @ iMax=MAX (column_id) FROM @ r; 
- 
WHILE @ i<=@ iMax 
The BEGIN 
SELECT @ oldName='t' + [name] 
='col', @ newName + LTRIM (@ I) 
The FROM @ r WHERE column_id=@ I; 
- 
The EXEC sp_rename @ oldName, @ newName 
The SET @ I=@ I + 1; 
END 

- 4. Validation 
SELECT * FROM t; 
/* 
Col1 col2 col3 
*/