Find Column In SQL

● Ian Grieve ●  ● 8 Comments   ● 

Microsoft Dynamics GPDuring 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 (http://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?

Please leave this field empty.

Your Name (required) -
Your Email (required) -
● Categories: Microsoft, SQL Server ● Tags: , , ,  ● Permalink ● Shortlink ●

8 Responses

  1. Steve Endow 3rd August 2016 / 19:49

    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

    • Ian Grieve Ian Grieve 11th August 2016 / 13:53

      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 *