How can i find out which tables in which databases have a column with a given name in SQL Server Management Studio?

Question

In SQL Server 2016 Management Studio, I have multiple databases.

I would like to find out which tables in which databases have a column whose name contains a string. How can i do that?

Is it possible to search within the Object Explorer window?

maybe query with join sys.objects, sys.all_columns on each database? — LONG, Mar 27 '17 at 16:01
Possible duplicate of [How to find column names for all tables in all databases in SQL Server](http://stackoverflow.com/questions/2729126/how-to-find-column-names-for-all-tables-in-all-databases-in-sql-server) — CodeCaster, Mar 27 '17 at 16:38
Please, please, please read [ask] and try searching before asking yet another question. Also, stop adding "Thanks" to every question. — CodeCaster, Mar 27 '17 at 16:38

John Cappelletti · Accepted Answer · 2017-03-27T16:37:32.690

3

You can use sp_MSForeeachdb

This approach will gather INFORMATION_SCHEMA.COLUMNS for each database on your server

Select * Into #TempFields From INFORMATION_SCHEMA.COLUMNS where 1=0

Declare @SQL varchar(max)=';Use [?]; Insert Into #TempFields Select * From INFORMATION_SCHEMA.COLUMNS '     
Execute master.sys.sp_MSforeachdb @SQL
Select * from #TempFields
 Where Column_Name Like '%XYZ%'

EDIT - Requested Commentary

The First query creates an EMPTY Structure to hold the Information_Schema

Then we DECLARE @SQL which contains the SQL we want to execute for each database. Notice the ;Use [?];

Then we simply select the #Temp table for the desired results

edited Mar 27 '17 at 16:37

answered Mar 27 '17 at 16:14

John Cappelletti

79,615
7
44
66

Thanks. Actually I would like to find out which tables in which databases have a column whose name contains a string, instead of a column with a given name. – Tim Mar 27 '17 at 16:20
1

@Tim If I understand your requirements, just change the final WHERE to Where Column_Name like '%XYZ%' – John Cappelletti Mar 27 '17 at 16:23
Thank you so much. Could you add some explanation of what the statements do? I understand the basic SQL commands, but not very sure what the commands in your code do. – Tim Mar 27 '17 at 16:32

Sparky · Answer 2 · 2017-03-27T16:20:33.600

You would need to iterate the databases on the server,

SELECT name FROM master.dbo.sysdatabases

performing the following command for each one, using

select table_name,COLUMN_NAME 
from INFORMATION_SCHEMA.columns 
where COLUMN_NAME like '% XXX %'

Doing it with a cursor

    CREATE TABLE #tmpList (DatabaseName varchar(64),TableName varchar(64))
    DECLARE @DbName varchar(64)
    DECLARE @sql nvarchar(max)

    DECLARE @ColCursor  CURSOR


    SET @colCursor = CURSOR FOR
        SELECT [name] FROM master.dbo.sysdatabases

    OPEN @ColCursor
    FETCH NEXT FROM @ColCursor INTO @DBName

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @sql = 'INSERT INTO #tmpList '+
                    'SELECT '''+@DbName+''',table_Name FROM '+@dbName+'.INFORMATION_SCHEMA.columns where COLUMN_NAME like ''% XXX %'' '
        EXEC(@sql)
        FETCH NEXT FROM @ColCursor INTO @DBName 
    END
    CLOSE @ColCursor;

select * from #tmpList
drop table #tmpList

LONG · Answer 3 · 2017-03-27T16:23:53.540

1

run the following on each database, or using cursor and dynamic sql for iterating each database

select A.name,B.name From sys.objects as A 
inner join sys.all_columns as B
on B.object_id = A.object_id

where A.type = 'U' and B.name = ...

update: for fuzzy lookup, use B.name like '%serarch_pttern%, %means any leading or trailing characters

edited Mar 27 '17 at 16:23

answered Mar 27 '17 at 16:05

LONG

4,490
2
17
35

Thanks. Actually I would like to find out which tables in which databases have a column whose name contains a string, instead of a column with a given name. – Tim Mar 27 '17 at 16:20

How can i find out which tables in which databases have a column with a given name in SQL Server Management Studio?

3 Answers3