Monday, 12 August 2019

Searching for 'Angularized values' in MS Sql Server DB

The following query can be used to look for 'Angularized values' in MS Sql Server DB.
DECLARE @sql nvarchar(1024)
DECLARE @column_name varchar(200)
DECLARE @table_name varchar(200)
DECLARE @sp_out varchar(2048)
DECLARE @x int

create table #tmp(sp_out varchar(2048), table_name varchar(255), column_name varchar(2048), id uniqueidentifier)
   
DECLARE tn_cursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.tables

open tn_cursor 
FETCH NEXT FROM tn_cursor
INTO @table_name

WHILE @@FETCH_STATUS = 0    
BEGIN   
                print @table_name

                DECLARE cn_cursor CURSOR FOR
                SELECT COLUMN_NAME
                FROM INFORMATION_SCHEMA.columns
                WHERE TABLE_NAME = @table_name

                open cn_cursor 
                FETCH NEXT FROM cn_cursor
                INTO @column_name

                WHILE @@FETCH_STATUS = 0    
                BEGIN   
                                FETCH NEXT FROM cn_cursor
                                INTO @column_name

                                --print @column_name

                                set @sql = 
                                N'insert into #tmp SELECT TOP (1000) ' + @column_name + ', ''' + @table_name + ''', ''' + @column_name + ''', Id' +
                                N' FROM [dbo].[' + @table_name + N']' +
                                N' WHERE ' + @column_name + N' like ''%? % ?%'''
                
                                --print @sql
                                exec sp_executesql @sql
                END

                CLOSE cn_cursor;    
                DEALLOCATE cn_cursor; 

                FETCH NEXT FROM tn_cursor
                INTO @table_name
END

CLOSE tn_cursor;    
DEALLOCATE tn_cursor; 

select * from  #tmp
drop table #tmp



This query will look for all fields in the DB with the contents with pattern '%? % %?' that matches Angularized values. An Angularized value is generated by AngularJs when autogenerated values are made when the contents of a drop down does not match the HTML.

No comments:

Post a Comment