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.

SQL SERVER - Validating Unique Column Name Across Whole Database bluelemon-800x800

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

SQL SERVER - Validating Unique Column Name Across Whole Database unique-column-name1

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

SQL SERVER - Validating Unique Column Name Across Whole Database unique-column-name2

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.

SQL Scripts, SQL Server, SQL System Table
Previous Post
SQL SERVER – Replace a Column Name in Multiple Stored Procedure All Together
Next Post
SQLAuthority News – Download SQL Server 2012 SP1 CTP4

Related Posts

7 Comments. Leave new

  • We can check using view INFORMATION_SCHEMA.COLUMNS.
    IF EXISTS
    ( SELECT *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE Column_Name = N’NameofColumn’)

    Reply
  • Yes sir, i have faced similar sitaution.when our development team produce new vesion release for exe or application,they have also changed for database. so for that we have to make single alter db script which can execute without fail.if column or table or any objects exists in production server then script will give error.for prevent this error and non technical person also can executes this script.

    Reply
  • These are the guys who never will undertand what is IT service development about…

    Reply
  • Hii,

    I have two tables and a same column name exist in both but ur second case doesn’t give a suitable result for one table. What’s the problem plzexplain/

    Reply
  • HI chinkey,

    you can use this alternative for your requirements.

    IF EXISTS
    ( SELECT *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE Column_Name = N’NameofColumn’ and Table_name = ‘NameOfTable’)

    Reply
  • The reason may be , they can use the joins without the alias of table.

    Reply
  • I have considered doing this in a database so that, when searching through source code that accesses the DB, I would only find code with the column name from the specific table I was looking for.
    E.g. we have a large DB with lots of columns named “Status”. When looking for all the code that references the “status” column for a specific table, it leads to lots of false positives.

    Reply

Leave a Reply