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.”
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 (http://blog.SQLAuthority.com)