Searching table with particular column


  • Hi All,

    I have list of columns (say col1,col2,col3). I need to find the table in the database which contains these 3
    columns. Please suggest.

    Sunday, September 15, 2013 11:41 AM


All replies

  • EXEC sp_msforeachdb 'select object_name(object_id) tblname,name from ?.sys.columns 
    where name in (''col1'')'

    Best Regards,Uri Dimant SQL Server MVP,

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, September 15, 2013 12:01 PM
  • Hi Jim,

    Try like this ,

    IF OBJECT_ID('tempdb..##SearchColumn') IS NOT NULL

    BEGIN DROP TABLE ##SearchColumn


    CREATE TABLE ##SearchColumn (ObjectName NVARCHAR(200)) EXECUTE sp_MSforeachdb ' IF ''?'' NOT IN (''master'',''model'',''Msdb'',''tempdb'') BEGIN USE [?] INSERT ##SearchColumn SELECT Object_name(object_id) FROM Sys.Columns WHERE name LIKE ''%BusinessEntityID%'' --ColumnName END' SELECT * FROM ##SearchColumn

    sathya - ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Sunday, September 15, 2013 1:02 PM
  • If I understand you correctly you what to list the tables that have all three columns. I think you can make a use of this query:

    select as table_name
    	, t.columns
    from sys.objects so
    	cross apply (
    			select + ';'
    			from sys.columns sc
    			where so.object_id = sc.object_id
    			for XML path ('')
    	) t(columns)
    where so.is_ms_shipped = 0
    	and so.type = 'U'
    	and t.columns like '%col1;%'
    	and t.columns like '%col2;%'
    	and t.columns like '%col3;%'

    Regards, Dean Savović

    Sunday, September 15, 2013 6:07 PM