Find Column In SQL

August 3rd, 2016 by

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'

Source: azurecurve

Leave a Reply