SQL SERVER – Validating Unique Column Name Across Whole Database

I sometimes come across very strange requirements and often I do not receive a proper explanation of the same. Here is the one of those examples. In this blog post we will discuss about how to validate unique column name across the whole database. The requirement was that column should not repeat the whole database.

Asker: “Our business requirement is when we add new column we want it unique across current database.”

Pinal: “Why do you have such requirement?”

Asker: “Do you know the solution?”

Pinal: “Sure, I can come up with the answer, but it will help me to come up with an optimal answer if I know the business need.”

Asker: “Thanks – what will be the answer in that case.”

Pinal: “Honestly, I am just curious about the reason why you need your column name to be unique across database.”

(Silence)

Pinal: “Alright – here is the answer – I guess you do not want to tell me reason.”

Option 1: Check if Column Exists in Current Database

IF EXISTS
(SELECT *
FROM sys.columns
WHERE Name = N'NameofColumn')
BEGIN
SELECT 'Column Exists'
-- add other logic
END
ELSE
BEGIN
SELECT 'Column Does NOT Exists'
-- add other logic
END

Option 2: Check if Column Exists in Current Database in Specific Table

IF EXISTS
(SELECT *
FROM sys.columns
WHERE Name = N'NameofColumn'
AND OBJECT_ID = OBJECT_ID(N'tableName'))
BEGIN
SELECT 'Column Exists'
-- add other logic
END
ELSE
BEGIN
SELECT 'Column Does NOT Exists'
-- add other logic
END

I guess user did not want to share the reason why he had a unique requirement of having column name unique across databases. Here is my question back to you – have you faced a similar situation ever where you needed unique column name across a database. If not, can you guess what could be the reason for this kind of requirement? 

Additional Reference: Query to Find Column From All Tables of Database

Reference: Pinal Dave (https://blog.sqlauthority.com), YouTube.