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.
7 Comments. Leave new
We can check using view INFORMATION_SCHEMA.COLUMNS.
IF EXISTS
( SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Column_Name = N’NameofColumn’)
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.
These are the guys who never will undertand what is IT service development about…
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/
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’)
The reason may be , they can use the joins without the alias of table.
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.