SQL SERVER – Validating Unique Columnname Across Whole Database
- by pinaldave
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
