Merhaba
Bu yazımızda SQL içerisinde aramak istediğimiz Column ve Value içerisinde genel bir tarama yapabilmek için gerekli olan kodu paylaşacağım.
2 adet değişkene sahip bir kurgusu var kodumuzun birinci değişken @search_item bu değişken ile hangi tablolara göre bir arama yaparken bi yandan @search_value değişkeni ile yazdığımızın değerin geçtiği tüm satırları görüntüleyebiliriz.
declare @sql varchar(max)
declare @sql_count varchar(max)
declare @search_item varchar(max)
set @search_item = 'Aramak istediğiniz Column buraya girilecek'
declare @search_value varchar(max)
set @search_value = 'değer arama için ise burayı kullanın '
declare @schema_name varchar(max)
declare @table_name varchar(max)
declare @column_name varchar(max)
declare @column_type_id int
DECLARE @rowcount TABLE (Value INT);
declare @count int
--tablo listesi
SELECT schema_table.TABLE_SCHEMA, schema_table.TABLE_SCHEMA, schema_table.TABLE_NAME, schema_table.TABLE_SCHEMA + '.' + schema_table.TABLE_NAME, schema_table.TabloKolonSayi, ''
FROM (SELECT name
FROM sys.tables
WHERE (is_ms_shipped = 0)) AS result INNER JOIN
(SELECT TABLE_SCHEMA, TABLE_NAME, COUNT(1) AS 'TabloKolonSayi'
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_SCHEMA, TABLE_NAME) AS schema_table ON result.name = schema_table.TABLE_NAME
WHERE (result.name LIKE '%' + @search_item + '%')
ORDER BY schema_table.TABLE_SCHEMA
--kolon listesi
SELECT
sch.TABLE_SCHEMA, tables.name as table_name, columns.name as column_name, columns.user_type_id, columns.max_length, columns.precision, columns.scale, columns.is_nullable
FROM
sys.all_columns as columns (NOLOCK),
SYS.tables as tables (NOLOCK),
(SELECT TABLE_SCHEMA, TABLE_NAME, COUNT(1) AS 'TabloKolonSayi'
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_SCHEMA, TABLE_NAME) as sch
WHERE
tables.object_id = columns.object_id
--AND tables.name like '%'+@search_item+'%'
and columns.name like '%'+@search_item+'%'
and sch.TABLE_NAME = tables.name
order by 1
--=============================================
--Kolonların value'larında arama
--=============================================
DECLARE cursor_value_list CURSOR FOR
SELECT
sch.TABLE_SCHEMA, tables.name as table_name, columns.name as column_name, columns.user_type_id
--, columns.max_length, columns.precision, columns.scale, columns.is_nullable
FROM
sys.all_columns as columns (NOLOCK),
SYS.tables as tables (NOLOCK),
(SELECT TABLE_SCHEMA, TABLE_NAME, COUNT(1) AS 'TabloKolonSayi'
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_SCHEMA, TABLE_NAME) as sch
WHERE
tables.object_id = columns.object_id
--AND tables.name like '%'+@search_item+'%'
and columns.name like '%'+@search_item+'%'
and sch.TABLE_NAME = tables.name
order by 1
OPEN cursor_value_list
FETCH NEXT FROM cursor_value_list INTO @schema_name, @table_name, @column_name, @column_type_id
WHILE @@FETCH_STATUS = 0
BEGIN
-- Step1 : Tablo kolon adetlerini elde et.
SET @sql = 'SELECT *
FROM
' + @schema_name + '.' + @table_name + ' (NOLOCK)
WHERE '
if @column_type_id = 167
begin
SET @sql = @sql + @column_name + ' LIKE ''%' + @search_value + '%'''
end
else
begin
SET @sql = @sql + @column_name + ' LIKE ''%' + @search_value + '%'''
end
SET @sql_count = REPLACE(@sql, 'SELECT * ', 'SELECT count(1) ')
--===================
-- Dinamik sorgudan adet çekmek
INSERT INTO @rowcount
Exec(@sql_count)
SELECT @count = Value FROM @rowcount
DELETE FROM @rowcount
--===================
--print @sql_count
IF @count > 0
begin
SELECT @schema_name + '.' + @table_name + '.' + @column_name AS 'SCHEMA.TABLE.COLUMN'
Exec(@sql)
end
-- END Operation
FETCH NEXT FROM cursor_value_list INTO @schema_name, @table_name, @column_name, @column_type_id
END
CLOSE cursor_value_list
DEALLOCATE cursor_value_list
