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'

Can ISC Software Solutions help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you’d like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.





Your Name (required):
Your Email (required):
Subject:
Enquiry message:

What should we write about next?





Your Name (required) –
Your Email (required) –

9 thoughts on “Find Column In SQL

  1. Pingback: Comment
  2. Pingback: Comment
  3. Pingback: Comment
  4. Pingback: Comment
  5. Comment 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. Comment 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

  6. Pingback: Comment
  7. Pingback: Comment
  8. Pingback: Comment

Leave a Reply to Comment Cancel reply

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