0

I want to add another column having a COUNT() subquery using the table and column names from TBL_A.TABLE_NAME and TBL_A.COLUMN_NAME respectively but the subquery does not accept the value from TBL_A.TABLE_NAME as a valid table object.

Currently using MS SQL server

SELECT TBL_A.TABLE_NAME
     , TBL_A.COLUMN_NAME
     , TBL_A.DATA_TYPE
     , (SELECT [TBL_A].[COLUMN_NAME] FROM [TBL_A].[TABLE_NAME] WHERE [TBL_A].[COLUMN_NAME] = 'Some Keyword')
FROM INFORMATION_SCHEMA.COLUMNS AS TBL_A;

I want to check if the keyword I entered is present in the specified tablename.columnname in INFORMATION_SCHEMA (e.g. COUNT > 0 thus is PRESENT)

jarlh
  • 42,561
  • 8
  • 45
  • 63
pansit99
  • 3
  • 2
  • Which DBMS are you using? – Nifriz May 07 '19 at 07:44
  • Skip the sub-query, do a LEFT JOIN instead. – jarlh May 07 '19 at 07:46
  • @Nifriz - Using Microsoft SQL Server – pansit99 May 07 '19 at 08:45
  • @jarlh - my database has dozen thousands of rows in INFORMATION_SCHEMA.COLUMNS. I cannot possibly LEFT JOIN all those tables. I want to use the values from TBL_A.TABLE_NAME and TBL_A.COLUMN_NAME as the table name and column name for the COUNT() sub-query. Please correct me if i am wrong? – pansit99 May 07 '19 at 08:53
  • Seems like you need to re-design your database. – jarlh May 07 '19 at 08:54
  • What you are looking for is a little big more compex https://stackoverflow.com/questions/436351/find-a-value-anywhere-in-a-database – EzLo May 07 '19 at 09:05

1 Answers1

0

Try this approach.
Note: It is supported in SQL Server (starting with 2008).

It uses a SELECT - OVER Clause and searches for Columns containing the desired keyword. 

SELECT [TBL_A].[TABLE_SCHEMA]  
    , TBL_A.TABLE_NAME  
    , TBL_A.COLUMN_NAME  
    , COUNT(*) OVER(PARTITION BY [TBL_A].[TABLE_SCHEMA], [TBL_A].[TABLE_NAME])  
        AS [Count in Current Table]  
    , COUNT(*) OVER(PARTITION BY [TBL_A].[COLUMN_NAME])  
        AS [Count in Current Database]  
FROM INFORMATION_SCHEMA.COLUMNS AS TBL_A  
WHERE [TBL_A].[COLUMN_NAME] LIKE '%Some Keyword%';
Milan
  • 294
  • 2
  • 10