During 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.
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
Hi Steve,
Yes, that looks to have all of the columns in which I wanted; all in one table too.
Thanks very much.
Ian