SQL SERVER SUPER QUERY Resurrection... - I just rescued the SUPER QUERY script and re-wrote it in order to list all more complete info on the records found. Basically it lists the pk column from the table containing the searched value, along with the pk value, table name and columnn where the searched value was found.This SUPER QUERY procedure is restricted to a especific schema named Global, but you can change that, altering (removing) this line:AND Schema_name(schema_id) = GlobalDoing so, the proc will search all your database on all columns that are CHAR, VARCHAR, NCHAR AND NVARCHAR...There you go:/* SELECT * FROM [Global].[Employee] EXEC spSearchGlobal @search_string=Domain1 */ALTER PROCEDURE Spsearchglobal(@search_string VARCHAR)AS BEGIN DECLARE @table_name SYSNAME, @table_id INT, @column_name SYSNAME, @id VARCHAR(100), @sql_string VARCHAR(2000) CREATE TABLE ##superquery ( id VARCHAR(100), pk VARCHAR(100), tabela VARCHAR(100), coluna VARCHAR(100) ) DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.tables WHERE type = U AND Schema_name(schema_id) = Global 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 SELECT @id = name FROM sys.columns WHERE object_id = Object_id(Global. + @table_name) AND is_identity = 1 SET @sql_string = INSERT INTO ##SuperQuery(id,pk,tabela,coluna) + SELECT + Isnull(@id+,, ) + + @id + + ,Global. + @table_name + + , + @column_name + + FROM Global. + @table_name + WHERE + @column_name + LIKE % + @search_string + % ; PRINT @sql_string 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 SELECT * FROM ##superquery DROP TABLE ##superquery END; Check out my new book on MariaDB ift.tt/1l8na5e
Posted on: Thu, 11 Sep 2014 13:42:09 +0000
Trending Topics
Recently Viewed Topics
© 2015