A Unique Column Name check lists existing matches across visible database metadata. I use it as an inspection, not an enforcement guarantee.
Someone asked me to prevent a column name from repeating anywhere in a database. I asked about the business need. The requester wanted the script but wouldn’t explain the reason. That unusual exchange prompted this example.
DECLARE @ColumnName sysname = N'NameofColumn';
SELECT OBJECT_SCHEMA_NAME(c.object_id) AS schema_name,
OBJECT_NAME(c.object_id) AS object_name, o.type_desc, c.name AS column_name
FROM sys.columns AS c
JOIN sys.objects AS o ON o.object_id = c.object_id
WHERE c.name = @ColumnName
ORDER BY schema_name, object_name;
SELECT CASE WHEN EXISTS
(SELECT 1 FROM sys.columns WHERE name = @ColumnName
AND object_id = OBJECT_ID(N'dbo.tableName'))
THEN 'Column Exists' ELSE 'Column Does NOT Exist' END AS table_check;The first query lists visible matches across catalog objects. The second checks one deliberately schema-qualified table. Metadata permissions and database collation influence what is visible and how names compare.
The original two checks
These historical images show the earlier IF/ELSE checks and their message spelling. The query above instead lists matching objects. Don’t expect the old picture’s message from a different query.
Another schema change can race with this pre-check. A required naming policy needs an enforcement and concurrency design. Have you encountered a business reason for such a policy?
Related reading
A naming pre-check is not a concurrency guarantee, it is a view of the metadata visible at that moment.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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.