Find all tables with column name

SQL_icon_base.jpgCommon situation, you need to make same changes in database, and you have column name.  But you aren't sure in which tables this column is, where to look etc. Here is the simple solution.

This is simple query that returns you list of all tables and schemas with specific column name. All you need is Copy & Paste and then change string value 'columnname' to name you are searching. This query find also similar columns, where part of columnname string is part of column name. If you are searching exactly specific column name change '%columnname%' into 'columnname'.

 

declare @SearchColumn as varchar(255)
SET @SearchColumn = '%columnname%'
SELECT 
t.name AS table_name
,SCHEMA_NAME(schema_id) AS schema_name
,c.name AS column_name
FROM 
sys.tables AS t
INNER JOIN sys.columns c 
ON t.OBJECT_ID = c.OBJECT_ID
WHERE 
c.name LIKE @SearchColumn

 


Add comment

Security code
Refresh