Maybe you've seen the blog post about the "super query" that locates a string on all text based columns on the database. I made some changes to it, after finding some errors when I re-used.
SELECT *
FROM global.unity
DECLARE @search_string VARCHAR(100),
@table_name SYSNAME,
@table_id INT,
@column_name SYSNAME,
@sql_string VARCHAR(2000)
SET @search_string = 'clinica'
DECLARE tables_cur CURSOR FOR
SELECT Schema_name(schema_id) + '.' + NAME,
object_id
FROM sys.objects
WHERE type = 'U'
OPEN tables_cur
FETCH next FROM tables_cur INTO @table_name, @table_id
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
DECLARE columns_cur CURSOR FOR
SELECT NAME
FROM sys.columns
WHERE object_id = @table_id
AND system_type_id IN ( 167, 175, 231, 239 )
OPEN columns_cur
FETCH next FROM columns_cur INTO @column_name
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name
+ ' WHERE ' + @column_name + ' LIKE ''%'
+ @search_string + '%'') PRINT ''' + @table_name
+ ', ' + @column_name + ''''
--PRINT @table_name
EXECUTE(@sql_string)
FETCH next FROM columns_cur INTO @column_name
END
CLOSE columns_cur
DEALLOCATE columns_cur
FETCH next FROM tables_cur INTO @table_name, @table_id
END
CLOSE tables_cur
DEALLOCATE tables_cur
Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06
Nenhum comentário:
Postar um comentário
Leave your comment here!