Search a Date String in All Columns in a Database


  

  DECLARE @SQL nvarchar(max)

declare @dtstring varchar(10)='2020-12-31'

 Select @SQL=Stuff((
SELECT  ' UNION ALL '+ char(13) + char(10)  + 'Select  ''' + c.COLUMN_NAME  +''' as '+  quotename(c.COLUMN_NAME)    +' , ''' + quotename(t.[TABLE_SCHEMA]) + '.' + quotename(t.[TABLE_NAME] ) 
+ ''' as tname, sum(case when ' + quotename(c.COLUMN_NAME)+   ' = '''+@dtstring+'''   then 1 else 0 End)  cnt      FROM   ' 
+ quotename(t.[TABLE_SCHEMA]) + '.' + quotename(t.[TABLE_NAME]) + ' WHERE 1=0 OR ' + quotename(c.COLUMN_NAME) + '= '''+@dtstring+''' '  
 + char(13) + char(10)  +' Group by  '+   quotename(c.COLUMN_NAME)+ char(13) + char(10) 
 +' Having sum(case when ' + quotename(c.COLUMN_NAME)+   ' = '''+@dtstring+''' then 1 else null End)>0 ' + char(13) + char(10)             

  FROM [INFORMATION_SCHEMA].[TABLES]  t join [INFORMATION_SCHEMA].[COLUMNS] c 
  on t.[TABLE_SCHEMA] =c.[TABLE_SCHEMA] and t.[TABLE_NAME]=c.[TABLE_NAME]  
  WHERE TABLE_TYPE='BASE TABLE' and [DATA_TYPE] Like '%date%' --and t.[TABLE_NAME] like'Event%'
FOR XML PATH(''), type ).value('.', 'varchar(max)'),1,10,'')

 Print @SQL
 Exec(@SQL) 

 

https://social.msdn.microsoft.com/Forums/en-US/42e2d4ac-e325-4e2d-b70e-a7f5843ec90e/finding-count-of-the-particular-value-in-all-the-columns-of-the-database?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s