The 3 best ways - Find a column in a SQL database

You want to find a column in one of the tables in your SQL Server database. Your database has a few hundred tables. Expanding all the tables to look for the column can be a time-consuming job. With these useful methods you find the column fast.

SQL Server has no search function to find a column in databases quickly. That can be useful when you look for a certain column but do not know which table it is in. The solution is a fairly simple query.

Method 1: SQL query on the metadata

1
SELECT O.NAME Tabelnaam,
2
C.NAME Kolomnaam
3
FROM sys.columns C
4
INNER JOIN sys.objects O ON C.object_id = O.object_id
5
WHERE C.NAME LIKE '%DebiteurNaam%'
6
ORDER BY O.NAME,
7
C.NAME

Of course it is not very practical to type this whole query every time you look for a column. That is why there is also this stored procedure, which makes the job very easy:

1
CREATE PROCEDURE [dbo].[pcdGetColumns]
2
@kolomnaam nvarchar(256)
3
-- Zoek een kolomnaam in een database
4
-- EXECUTE pcdGetColumns '%naam%'
5
AS
6
BEGIN
7

8
SELECT O.NAME Tabelnaam,
9
C.NAME Kolomnaam
10
FROM sys.columns C
11
INNER JOIN sys.objects O ON C.object_id = O.object_id
12
WHERE C.NAME LIKE @kolomnaam
13
ORDER BY O.NAME, C.NAME
14
END

Method 2: Red-Gate SQL Search

This is a really useful little tool from the vendor Red-Gate. I am a big fan of the tools Red-Gate sells anyway. Sadly they are not free and quite pricey, especially if the company you work for or do a project for does not have the tools. Luckily this tool is completely free. It is called SQL Search 2.

You install SQL Search 2 as an add-on in SQL Server Management Studio. You then get an extra button at the top that lets you search all databases, and also within different objects. You can choose to search only in tables, but you can also search in views, functions, procedures and triggers. Very cool.

I hope this helps you find a column in your database. If you have no rights to install software, choose the SQL query. If you can install software, choose the free SQL Search from Red-Gate.