We have some pretty large databases in our system and at times I need to find all columns in a database that have the same name.
DECLARE @ColName VARCHAR(200) = '%cert%' SELECT Obj.Name AS [TableName], Col.name AS [ColumnName] FROM DBO.SysObjects Obj INNER JOIN DBO.SysColumns Col ON Col.Id = Obj.Id WHERE Col.Name LIKE @ColName
Here are my results for all columns that have “cert” in the name.
2 thoughts on “SQL Tool: Find all columns, by name, in database.”
I'm presuming this is for Microsoft SQL Server, given DBO.SysObjects.
This seems like an argument in favor of using consistent column names across your database.
Yup. It is great when things are consistent. I have another one I'm going to edit and get up here eventually that looks at each row – in each column – in each table across the database. It's a slow but through way of finding things like unique keys.