1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
|
declare @myStringToSearch varchar(100)
set @myStringToSearch = 'maRecherch'
DECLARE allColumns_cursor CURSOR FOR
SELECT C.[name] as colName
,T.name as tableName
FROM [sys].[columns] C
JOIN [sys].[tables] T
ON T.[object_id] = C.[object_id]
where T.is_ms_shipped = 0
and System_type_id in (35,99,167,231)
declare @colName varchar(100)
declare @tableName varchar(100)
declare @working_tableName varchar(100)
declare @selectList varchar(max)
declare @whereList varchar(max)
declare @sql varchar(max)
OPEN allColumns_cursor
FETCH NEXT FROM allColumns_cursor
into @colName, @tableName
WHILE @@FETCH_STATUS = 0
begin
if coalesce(@tableName,'') != coalesce(@working_tableName,'')
begin
if not @working_tableName is null
begin
set @sql =@selectList+ ' from ' + @working_tableName + ' ' + @wherelist
EXEC(@sql)
end
set @working_tableName = @tableName
set @selectList = 'select ' + @colName
set @whereList = 'where (' + @colName + ' like ''%' + @myStringToSearch + '%'')'
end
else
begin
set @selectList = @selectList + ', ' + @colName
set @whereList = @whereList + 'or (' + @colName + ' like ''%' + @myStringToSearch + '%'')'
end
FETCH NEXT FROM allColumns_cursor
into @colName, @tableName
END
if not @working_tableName is null
begin
set @sql =@selectList+ ' from ' + @working_tableName + ' ' + @wherelist
EXEC(@sql)
end
CLOSE allColumns_cursor
DEALLOCATE allColumns_cursor |
Partager