HOW TO LOOP ALL DATABASES TO GET REQUIRED COLUMN DETAILS

SET ANSI_WARNINGS OFF;
SET FMTONLY OFF;
SET NOCOUNT ON
DECLARE  @Db_name TABLE( database_name  NVARCHAR(MAX),id INT IDENTITY(1,1))

INSERT INTO @Db_name
SELECT name  FROM sys.databases WHERE name NOT IN  ('SQL_ADMIN_DB','master','EDWARD','Policy_Accounting_Production'
,'Lookup_Tables_Db','ignite_repository','AdventureWorks')

--SELECT * FROM  @Db_name

DECLARE @cnt INT
SELECT@cnt = COUNT(*)  FROM @Db_name

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp

CREATE TABLE  #temp( database_name  VARCHAR(MAX),table_name  VARCHAR(MAX),column_name VARCHAR(MAX))

--select @cnt
While (@cnt>0)
BEGIN
DECLARE @ddbname VARCHAR(100)
DECLARE @sql NVARCHAR(max) =' '


    SELECT @ddbname=database_name FROM @Db_name WHERE id=@cnt
  
      --SELECT @ddbname
    SET @sql=@sql+' INsert into #temp( database_name,table_name,column_name) Select  table_catalog,table_name,column_name  from   '+  @ddbname+'.information_schema.columns where column_name like ''%card%''  '

      EXEC (@sql )

     DELETE FROM @Db_name WHERE id=@cnt
  
  
SET @cnt=@cnt-1
END

;WITH cte_table AS(
SELECT database_name ,table_name,column_name FROM #temp)
--INSERT INTO Token_Management.dbo.Token_Schema_details
--(Database_name,Table_name,column_name)
SELECT Database_name,Table_name,column_name   FROM cte_table
WHERE table_name NOT LIKE 'sync%'
AND column_name NOT IN ( 'card_account_type','card_type','Card_type_id','pm_card_type','card_category',
'cardholder_name','card_payment_account_id','previous_card_type_id','card_type_cd','card_type_desc'
,'timecard_acknowledged_flag','CreditCardID','CreditCardApprovalCode','card_number_is_scrambled','CardType')


------------------------------Another simple way to use msforeachdb 



Create table #yourcolumndetails(DBaseName varchar(100)
                                                      , TableSchema varchar(50)
                                                     , TableName varchar(100)
                                                     ,ColumnName varchar(100)
                                                     , DataType varchar(100)
                                                     , CharMaxLength varchar(100)
                                                   )

EXEC sp_MSForEachDB @command1='USE [?];
    INSERT INTO #yourcolumndetails SELECT
    Table_Catalog
    ,Table_Schema
    ,Table_Name
    ,Column_Name
    ,Data_Type
    ,Character_Maximum_Length
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE Table_Name like ''%StringMapBase%'''  

select * from #yourcolumndetails
Drop table #yourcolumndetails