Find Column In SQL

Microsoft SQL ServerDuring a recent upgrade we encountered an error which resulted in me poking around in the database attempting to locate column called PAYRCORD.

This is not something I am going to do manually, so I wrote a script which would find all tables containing the specified column (change the highlighted section):

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
DECLARE @ColumnToFind VARCHAR(20) = 'PAYRCORD'
SELECT
	SCHEMA_NAME(t.schema_id) AS 'Schema'
	,t.name AS 'Table'
FROM
	sys.tables AS t
INNER JOIN
	sys.columns AS c
		ON
			t.OBJECT_ID = c.OBJECT_ID
WHERE
	c.name = @ColumnToFind
ORDER BY
	'Schema'
	,'Table'

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

9 thoughts on “Find Column In SQL

  1. Steve Endow says:

    Hi Ian,

    Would INFORMATION_SCHEMA do the job? I find it much easier to use than sysobjects.

    SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ‘PAYRCORD’

    Steve

    1. Ian Grieve says:

      Hi Steve,

      Yes, that looks to have all of the columns in which I wanted; all in one table too.

      Thanks very much.

      Ian

Leave a Reply

Your email address will not be published. Required fields are marked *