SQL SERVER – Validating Unique Columnname 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.

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:
SQL SERVER – Query to Find Column From All Tables of Database

Click to Download Scripts

Reference: Pinal Dave (http://blog.SQLAuthority.com)

8 thoughts on “SQL SERVER – Validating Unique Columnname Across Whole Database

  1. 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.

    Like

  2. 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/

    Like

  3. 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’)

    Like

  4. Pingback: Dew Drop – September 24, 2012 (#1,407) | Alvin Ashcraft's Morning Dew

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #048 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s