Whenever I’m considering making database schema changes, I often find the need to search the database I’m working with for columns with a particular name.
It can be especially valuable to do this when dealing with a large model consisting of many tables and columns. In this situation, I’m typically seeking to establish whether a similar column already exists or perhaps attempting to build up a general picture of how the tables within the database are connected to each other.
Invalid object name 'Foo'.
If you’ve ever seen the above error message following a software deployment, then you’ll also appreciate having the ability to quickly search for columns by their name. In this scenario, you can look for the column within a good version of the database to see what you’re missing in production.
In this article, I demonstrate a quick and easy way of searching for columns using a simple SQL query.
The Query
You can run the query below against a SQL Server database to find existing columns which may be related to the new column (or columns) you are creating or to the feature you are developing.
DECLARE @ColumnName AS VARCHAR(MAX) SET @ColumnName = '%COLUMN_TO_FIND%' SELECT sys.schemas.name AS [Schema], sys.tables.name AS [Table], sys.columns.name AS [Column], sys.types.name AS [Data Type], sys.columns.max_length AS [Length], sys.columns.is_nullable AS [Is Nullable] FROM sys.schemas INNER JOIN sys.tables ON sys.schemas.schema_id = sys.tables.schema_id INNER JOIN sys.columns ON sys.tables.object_id = sys.columns.object_id INNER JOIN sys.types ON sys.columns.user_type_id = sys.types.user_type_id WHERE sys.columns.name LIKE @ColumnName ORDER BY sys.tables.name, sys.columns.name
Before executing the SQL, first of all, make sure that you update the value of the @ColumnName variable (the '%COLUMN_TO_FIND%' text) with the name, or partial name, of the column you are looking for.
The Results
After running the query you will see a result set with the matching column names, along with both the schema and the name of the associated table that each matching column belongs to.
If you run the query against the AdventureWorks database when searching for the text '%product%', the output will look similar to the screenshot below.
The results are helpfully ordered by the table name, then by the column name so that you can easily find what you are looking for.
Some bonus columns such as the column data type and length are also included. I find these metrics are useful when searching for a foreign key column or a notes column, for example.
Alternatives
There are other ways of getting similar information to the above from your database e.g. you can select from the INFORMATION_SCHEMA.COLUMNS system table and your query will then work across any database which adheres to the ANSI Standard.
I find it useful, however, when working with SQL Server databases to include some of the extra, non-standard, information which the above query is able to provide.
Usage
Feel free to use and amend this script as you see fit. There is scope to select additional information such as table creation/modification dates and a number of other lower-level attributes.
Comments