Find All Tables Containing Column With Specified Name - Ms SQL Server

Find all table names with column name?

Please try the below query. Use sys.columns to get the details :-

SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%MyCol%';

How to find all tables and datasets/databases which have a specific column name in big query

I found the solution is to replace the dataset name with region-us instead.

The below works for looking up across tables and datasets

SELECT
    ddl 
FROM
    `project-name`.`region-us`.INFORMATION_SCHEMA.TABLES
WHERE 
    table_name like '%sender%' 
    AND ddl LIKE '%sender_country%' 

The below works for views:

SELECT
    ddl 
FROM
    `project-name`.`region-us`.INFORMATION_SCHEMA.VIEWS
WHERE 
    table_name like '%sender%' 
    AND ddl LIKE '%sender_country%' 

Searching all tables in database containing specific column

Using INFORMATION_SCHEMA.COLUMNS:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'id'

SQL Server 2008: find all tables containing columns with specified name

Group by the content that is unique and count the group content in the having clause

select t.name
from sys.tables t 
inner join sys.columns c on c.object_id=t.object_id 
where c.name in ('student','teacher')
group by t.name
having count(distinct c.name) = 2

Find all Tables that contain Column Name; Filter Empties

Something like this may work without huge effort:

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
            ,p.rows
FROM sys.columns c
INNER JOIN sys.tables t
ON c.object_id = t.object_id
INNER JOIN sys.partitions p
on t.object_id = p.object_id
WHERE       c.name LIKE '%p%'
            AND p.rows > 0
ORDER BY    TableName
            ,ColumnName;

Just note that sys.partitions isn't guaranteed to be accurate about row counts; sys.dm_db_index_physical_stats may be better (see https://dba.stackexchange.com/questions/55124/how-accurate-is-the-sys-partition-rows-column). This might give you a better count but may have more locking issues if you're using AlwaysOn:

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
            ,ips.record_count
FROM sys.columns c
INNER JOIN sys.tables t
ON c.object_id = t.object_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), t.object_id, null, null, 'DETAILED') ips
WHERE       c.name LIKE '%p%'           
            AND ips.record_count > 0
ORDER BY    TableName
            ,ColumnName;

If you really need 100% reliability on this, you'd have actually do a COUNT(*) on each table (using Dynamic SQL) you want to check, but this is probably good enough.

How to search a column name in all tables in a database in SQL Server 2012?

Try the below

Select distinct object_name(object_id), name from sys.columns where name like '%tax%'

or

select table_name, Column_name from Information_Schema.Columns where Column_Name like '%Tax%'

---

Related Topics