August 1, 2016 at 1:46 am
Hello Everyone,
I have a problem, We have different client specific databases with all same Tables, sps and functions. And we write standard Db scripts so that it can be run on all DBs.
But sometimes for some CR we alter a table by Adding/Removing columns. And when we run the standard script
It throws error stating "No column" or so..
SO Is there way, where i can can check for the columns in table first and if the columns are there the script should execute else the it should skip the script and proceed with the next one.
Thanks in advance.
Sanjay
August 1, 2016 at 2:15 am
Quick example
😎
USE TEEST;
GO
SET NOCOUNT ON;
-- THE NAME OF THE COLUMN WHICH WE WANT TO CHECK IF EXISTS OR NOT
DECLARE @COL_TO_LOOK_FOR NVARCHAR(128) = N'MY_NEW_COL';
-- TABLE THAT DOES NOT HAVE THE COLUMN WE ARE CHECKING FOR
IF OBJECT_ID(N'dbo.TBL_TEST_COL_EXIST') IS NOT NULL DROP TABLE dbo.TBL_TEST_COL_EXIST;
CREATE TABLE dbo.TBL_TEST_COL_EXIST
(
TCE_ID INT NOT NULL CONSTRAINT PK_DBO_TBL_TEST_COL_EXIST_TCE_ID PRIMARY KEY CLUSTERED
);
INSERT INTO dbo.TBL_TEST_COL_EXIST(TCE_ID) VALUES(1);
-- CHECK IF THE COLUMN EXISTS
IF (
SELECT
COUNT(*) AS COL_COUNT
FROM sys.columns SCOL
WHERE SCOL.object_id = OBJECT_ID(N'dbo.TBL_TEST_COL_EXIST')
AND SCOL.name = @COL_TO_LOOK_FOR) > 0
BEGIN
SELECT
TCE.MY_NEW_COL
FROM dbo.TBL_TEST_COL_EXIST TCE
END;
-- ADD THE COLUMN
ALTER TABLE dbo.TBL_TEST_COL_EXIST ADD MY_NEW_COL INT NOT NULL DEFAULT (1001);
-- CHECK AGAIN
IF (
SELECT
COUNT(*) AS COL_COUNT
FROM sys.columns SCOL
WHERE SCOL.object_id = OBJECT_ID(N'dbo.TBL_TEST_COL_EXIST')
AND SCOL.name = @COL_TO_LOOK_FOR) > 0
BEGIN
SELECT
TCE.MY_NEW_COL
FROM dbo.TBL_TEST_COL_EXIST TCE
END;
August 2, 2016 at 4:01 am
Thanks a lot for the solution...:-)
August 2, 2016 at 5:35 am
You are very welcome and thanks for the feedback.
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply