CODEWITHSUNDEEP
sql-server
jeff
jeff

Reputation: 101

Search database for all column names with specific values

We have a vendor specific application that has 100's of tables and most of them have a column called Company_Code and within that column is stored a value for each company. Is it possible to search the entire database where COMPANY_CODE = 'TST' and return those values?

Upvotes: 0

Views: 78

Answers (1)

Rodney Ellis
Rodney Ellis

Reputation: 807

This should do the trick: 

DECLARE @Value varchar(100) = 'TST';
DECLARE @script varchar(1000);
CREATE TABLE ##Results (TableName VARCHAR(100) , ValueCount INT);

DECLARE db_cursor CURSOR FOR 

WITH cte AS
(
SELECT o.NAME AS TableName , c.name  As ColumnName
FROM sys.sysobjects o 
JOIN sys.syscolumns c on o.id = c.id 
WHERE c.name = 'COMPANY_CODE '
)
SELECT 'INSERT INTO ##Results (TableName , ValueCount) SELECT ''' + TableName + ''' , COUNT(*) FROM ' + TableName + ' WHERE ' +  ColumnName + ' = ''' + @Value + '''' AS Script
FROM cte; 

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @script  

WHILE @@FETCH_STATUS = 0  
BEGIN  
      PRINT @script;

      EXEC (@Script);

      FETCH NEXT FROM db_cursor INTO @script 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 


SELECT * FROM ##Results WHERE ValueCount > 0

Upvotes: 2

Related Questions