Last year I posted a script to find tables containing a particular column using sys objects. Steve Endow of Dynamics GP Land suggested using the INFORMATION_SCHEMA instead as he found it easier to use.
I’ve recently had reason to search for tables with a particular column in them, so I took a look at using a script using INFORMATION_SCHEMA.COLUMNS. However, when taking a detailed look at the results I found a few anomalies; the issue was that INFORMATION_SCHEMA.COLUMNS returns results for columns in not only tables, but also views. Which does make sense as both tables and views have columns. For what I was working on I needed a list of only tables.
I did a little exploring of the INFORMATION_SCHEMA and determined that I could join to INFORMATION_SCHEMA.TABLES and filter on TABLE_TYPE <> ‘VIEW’ to get a result set of only tables:
/*
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 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @ColumnToFind VARCHAR(20) = 'PAYRCORD'
SELECT
['Tables'].TABLE_SCHEMA AS 'Schema'
,['Tables'].TABLE_NAME AS 'Table'
FROM
INFORMATION_SCHEMA.COLUMNS AS ['Columms']
INNER JOIN
INFORMATION_SCHEMA.TABLES AS ['Tables']
ON
['Tables'].TABLE_CATALOG = ['Columms'].TABLE_CATALOG
AND
['Tables'].TABLE_SCHEMA = ['Columms'].TABLE_SCHEMA
AND
['Tables'].TABLE_NAME = ['Columms'].TABLE_NAME
AND
['Tables'].TABLE_TYPE <> 'VIEW'
WHERE
COLUMN_NAME = @ColumnToFind
ORDER BY
'Schema'
,'Table'
In the original posts script I was using the sys objects directly, but was filtering out the views by joining to sys.tables which contains only tables. Both the original script and the above one return exactly the same result set.
So, what’s the difference?
INFORMATION_SCHEMA, or System Information Schema Views to give the full name, is one of several methods SQL Server provides to get an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes may have been made to the underlying system tables. The information schema views included in SQL Server originally complied with the ISO standard definition for the INFORMATION_SCHEMA, but appear to have diverged from the standard as new standards have been introduced.
The metadata returned by INFORMATION_SCHEMA, comes from the sys objects. So by using the former you are getting information from the latter, but in a way which should be future proofed against database changes.