Check If column exists or not in table and rename them-CodePudding

I am trying to merge two different rules into one that should run in two databases server.

I did merged the files. but i am stuck at the this columns. There are two columns with different names but the same data in the two databases. I want my query to check if if this column exist

First column - APE.RecProdOwner
Second Column - APE.ReconciliationOwner

I want my query to check for both these columns in the database and if one of the two exists then it should store the data into the new column name as “RecProdOwner”

CodePudding user response：

You can use an EXISTS against the sys objects, and then simply use sys.sp_rename to rename the column:

USE Sandbox;
GO

CREATE TABLE dbo.YourTable ([APE.RecProdOwner] int,
                            ReconciliationOwner int);
GO


IF EXISTS (SELECT 1
           FROM sys.schemas s
                JOIN sys.tables t ON s.schema_id = t.schema_id
                JOIN sys.columns c ON t.object_id = c.object_id
           WHERE s.name = N'dbo'
             AND t.name = N'YourTable'
             AND c.name = N'APE.RecProdOwner')
    EXEC sys.sp_rename N'dbo.YourTable.[APE.RecProdOwner]','RecProdOwner','COLUMN';

IF EXISTS (SELECT 1
           FROM sys.schemas s
                JOIN sys.tables t ON s.schema_id = t.schema_id
                JOIN sys.columns c ON t.object_id = c.object_id
           WHERE s.name = N'dbo'
             AND t.name = N'YourTable'
             AND c.name = N'APE.ReconciliationOwner')
    EXEC sys.sp_rename N'dbo.YourTable.[APE.ReconciliationOwner]','ReconciliationOwner','COLUMN';
GO
SELECT *
FROM dbo.YourTable;
GO

DROP TABLE dbo.YourTable;