为每列添加前缀

Question

我有一个包含许多列的表，我希望为每个列添加前缀abc_。

我尝试了以下查询：

    SELECT syscolumns.name as old_column_name, 'ABC_' + syscolumns.name as new_column_name
    FROM sysobjects 
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    WHERE sysobjects.name = 'ABCtable'
    ORDER BY sysobjects.name,syscolumns.colid

Answer 1

你可以试试这样的东西。

    DECLARE @Colums AS TABLE(IndexNo INT IDENTITY(1,1), OldCol VARCHAR(MAX), NewCol VARCHAR(MAX))
    INSERT INTO @Colums
    SELECT syscolumns.name as old_column_name, 'ABC_' + syscolumns.name as new_column_name
    FROM sysobjects 
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    WHERE sysobjects.name = 'ABCtable'
    ORDER BY sysobjects.name,syscolumns.colid


    DECLARE @I INT=1;
    WHILE EXISTS(SELECT * FROM @Colums WHERE IndexNo=@I)
    BEGIN
        DECLARE @OldCol VARCHAR(MAX)='', @NewCol VARCHAR(MAX)=''
        SELECT @OldCol='ABCtable.'+OldCol, @NewCol=NewCol FROM @Colums WHERE IndexNo = @I

        EXEC sp_rename @OldCol, @NewCol, 'COLUMN'

        SET @I = @I+1;
    END