Search for a specific value in a common named column

August 21, 2018 3,367d Comments

Sometimes, you need to find some data in a specificly named column, like what changed by a user, in the “LAST_CHANGED_BY” columns.
If you name the column, and the specific value you search, this script gives you a list of tables of how many rows any tables have with the matching values in matching columns.

DECLARE @columnname varchar(100) = 'LAST_CHANGED_BY';
DECLARE @columnvalue nvarchar(max) = 'USERNAME';

CREATE TABLE #TemporaryData
(
	TableName varchar(1000),
	RowNum int
);

DECLARE @TablesToSearch TABLE 
(
	ColumnName varchar(1000),
	TableName varchar(1000)
);

INSERT INTO @TablesToSearch (ColumnName, TableName)
SELECT c.name  AS 'ColumnName' ,t.name AS 'TableName'
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name = @columnname;

DECLARE @tablename nvarchar(max), @sql nvarchar(max);

DECLARE table_cursor CURSOR FOR   
SELECT  TableName
FROM 	@TablesToSearch;

OPEN table_cursor  ;

FETCH NEXT FROM table_cursor   
INTO @tablename  ;

WHILE @@FETCH_STATUS = 0  
BEGIN  

	SET @sql = 'INSERT  #TemporaryData (TableName, RowNum)
				SELECT ''' + @tablename + ''', COUNT(1)
				FROM ' + @tablename + '
				WHERE CAST(' + @columnname + ' as nvarchar(max)) = ''' + @columnvalue + ''';
				'
	exec sp_executesql @sql;
	
    FETCH NEXT FROM table_cursor   
    INTO @tablename  
END   
CLOSE table_cursor;  
DEALLOCATE table_cursor; 
	
SELECT * FROM #TemporaryData ORDER BY RowNum DESC;
DROP TABLE #TemporaryData;

3 thoughts on “Search for a specific value in a common named column”

  48. Thank you for each of your labor on this blog. My mom really likes getting into internet research and it’s easy to understand why. A number of us learn all relating to the powerful mode you present vital steps on this website and therefore attract participation from other ones about this concept then my daughter has been starting to learn a lot. Take pleasure in the rest of the year. You have been carrying out a splendid job.

    Reply

  57. Thanks a lot for providing individuals with an exceptionally spectacular possiblity to read articles and blog posts from this website. It is always very pleasurable and also jam-packed with a great time for me personally and my office acquaintances to search your web site at a minimum three times in 7 days to read through the fresh guides you have got. Of course, I’m certainly amazed with your beautiful ideas served by you. Selected 1 tips on this page are ultimately the most suitable we have had.

    Reply

  68. I am also writing to make you be aware of what a great encounter my princess gained browsing the blog. She noticed a good number of pieces, which include what it’s like to have a marvelous coaching style to make many more easily completely grasp selected specialized subject matter. You actually surpassed her desires. Many thanks for displaying the productive, healthy, explanatory and even easy guidance on the topic to Evelyn.

    Reply

  636. Farklı cialis fiyat’larda satışa sunulmasındaki en büyük etkenlerden biri de satıcıların belirlemiş olduğu cialis fiyat politikalarıdır. Özellikle zaman zaman yapılan kampanyalar, Cialis fiyat 2019 tarih aralığında değişiklik oluşmasına neden olabilir.

    Reply