Thursday, February 09, 2023

How to Find Tables that Contain a Specific Column in SQL Server?

Basic concept to understand about SQL Server is that of catalog views, which are effectively database tables (catalogs in this case) that display system-wide information about the SQL Server Database Engine.

All catalog views are accessed via a SELECT SQL statement FROM a specific catalog within the sys. namespace.
For example, the following statement can be used to view information about all database tables in the system via the sys.tables catalog

use mobility
        s.[name]            'Schema',
        t.[name]            'Table',
        c.[name]            'Column',
        d.[name]            'Data Type',
        c.[max_length]      'Length',
        d.[max_length]      'Max Length',
        d.[precision]       'Precision',
        c.[is_identity]     'Is Id',
        c.[is_nullable]     'Is Nullable',
        c.[is_computed]     'Is Computed',
        d.[is_user_defined] 'Is UserDefined',
        t.[modify_date]     'Date Modified',
        t.[create_date]     'Date created'
from        sys.schemas s
inner join  sys.tables  t
on s.schema_id = t.schema_id
inner join  sys.columns c
on t.object_id = c.object_id
inner join  sys.types   d
on c.user_type_id = d.user_type_id
where like '%ProjectManagerID%'

Hope this helps!!