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

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

Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology