Check If column exists or not in table and rename them

Question

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”

Currently I am using this - if exists( select * from information_schema_columns where table_name = bc_AccountPool_Extra and column_name = Ape.RecProdOwner’) — 5aadat, Jun 13 '22 at 08:16
What is "this"..? You've tagged *both* [[tag:mysql]] and [[tag:sql-server]], which are you *really* using? — Thom A, Jun 13 '22 at 08:17
That doesn't tell us what RDBMS you are using (and attempts should be [edit]s to the question as well). — Thom A, Jun 13 '22 at 08:19
https://stackoverflow.com/questions/167576/check-if-table-exists-in-sql-server Seem to answer the question (in question ;) ) — Griffin, Jun 13 '22 at 08:27

score 2 · Accepted Answer · answered Jun 13 '22 at 08:26

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;

Check If column exists or not in table and rename them

1 Answers1