Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save lcomplete/76182ac306c2fe1f2be97c6bc480b4ab to your computer and use it in GitHub Desktop.
Save lcomplete/76182ac306c2fe1f2be97c6bc480b4ab to your computer and use it in GitHub Desktop.
USE db
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(600),@TableRowCount int,@ColumnKey nvarchar(128),@DbName nvarchar(50);
SET @TableName = ''
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = '';
set @ColumnKey = '';
SELECT @TableName = MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), @DbName =min(TABLE_CATALOG)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
if (@TableName IS NOT NULL and PARSENAME(@TableName, 1) != 'ScanResult')
BEGIN
SELECT @TableRowCount = SUM(st.row_count)
FROM sys.dm_db_partition_stats st
WHERE object_name(object_id) = PARSENAME(@TableName, 1)
AND (index_id < 2)
select @ColumnKey = column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('int')
and ORDINAL_POSITION = 1
print 'key:'+@ColumnKey
if @TableRowCount < 20000 and @ColumnKey != ''
begin
SET @ColumnName =
(
SELECT COLUMN_NAME + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('varchar', 'nvarchar')
for xml path('')
)
print 'cols: '+@ColumnName
declare @CursorSql nvarchar(500);
declare @Json nvarchar(4000);
declare @TableKey int;
set @CursorSql = 'declare cur CURSOR for select ' + @ColumnKey + ', (select ' + @ColumnName +
@ColumnKey +
' for json path,WITHOUT_ARRAY_WRAPPER) from ' + @TableName + ' with (nolock) ';
print @CursorSql
exec sp_executesql @CursorSql
open cur
fetch next from cur into @tablekey,@Json;
while @@fetch_status = 0 begin
if exists(select * from ScanKey where @json like '%' + KeyStr + '%') and not exists (select * from ScanResult
where TableKey=@TableKey and TableName=quotename(@DbName)+'.'+ @TableName)
begin
insert into ScanResult (tablekey, jsonresult, tablename, createtime)
values (@TableKey, @Json, quotename(@DbName)+'.'+ @TableName, getdate())
end
fetch next from cur into @TableKey,@Json
end
close cur;
deallocate cur;
end
END
END
select *
from ScanResult
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment