Recently I have been asked to merge a new datasource in my current SQL Server solution for client. For example I had data from US in enterprise data warehouse solution, now clients wants to add UK data in same dimension and facts tables.
Now UK database could be in a complete different structure and I will be looking each column to find if Dimension Tax Type data and then do a SELECT DISTINCT Col_Value FROM UK.Table_X. Or I could automate and quickly find all the columns in UK database which hold anything like Tax.
Searching a small text in a whole database could be for multiple reasons:
- Merging new data source
- Looking for something in unknown database
- Understanding different language datasource etc.
So following is the code for same. Use it, utilise it, enhance it, extract whatever good you can from it.
Let me know if you want clarity on any section. I have given good detailed comments for understanding, still comments section is open for any query and feedback.
Inputs are only needed in section 1 (and Section 4 but only if desired).
/*--- Section 1: Declare variables to be provieded for search-----*/
DECLARE @SearchString nvarchar(MAX)
DECLARE @test_tableschema varchar(5)
/*--- Section 2: Provide values for search---*/
SET @SearchString = 'Tax'
SET @test_tableschema = 'dbo'
/*--- Section 3: Start procedure. Declare variable for filtering attributes from database
on which you want to search text---*/
--DECLARE @FilterTable table (TableName varchar(100))
DECLARE @Col_list table (TableName varchar(100),ColName varchar(100), RID INT Identity(1,1))
/*--- Section 4: This part has SQL query to get a list of columns from tables you want to check text in ---
---You can be more creating and filter tables or columns based on name (first two commented lines)
---You can filter columns by columns data types to make sure least count of columns are actually checked
---You can use where clause with T.Table_Name like 'a%' to check only tables with name starting with A
In case of big database we can pick few alphabets to check the data.
---This whole section is for filtering least column (or keep all) from overall column list in database
which in turns help in quick search
*/
--INSERT INTO @FilterTable (TableName)
--VALUES ('UTILICA_forbolag'),('UTILICA_forkategori'),('UTILICA_foromfattning');
INSERT INTO @Col_list(TableName, ColName)
SELECT (T.TABLE_NAME),C.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS AS C
INNER JOIN INFORMATION_SCHEMA.TABLES T
ON C.TABLE_NAME = T.TABLE_NAME AND C.TABLE_SCHEMA = T.TABLE_SCHEMA AND T.TABLE_TYPE = 'BASE TABLE' AND T.TABLE_SCHEMA = @test_tableschema
--WHERE T.TABLE_NAME in (SELECT DISTINCT TableName from @FilterTable);
/*--- Section 5: No need to change anything below. Declared variables for usage in final query ---*/
DECLARE @test_table varchar(max)
DECLARE @test_columnname varchar(max)
DECLARE @Final_Result table (TableName varchar(100),ColName varchar(100), Found_Flag int, QuickQuery varchar(250))
DECLARE @ROWID INT
DECLARE @found_output INT
DECLARE @Flag_Query nvarchar(max)
SET @ROWID = 1
WHILE Exists (Select RID from @Col_list)
BEGIN
SELECT @test_table = TableName, @test_columnname = ColName FROM @Col_list WHERE RID = @ROWID
SET @Flag_Query = 'SELECT @found_flag = MAX(CASE WHEN '+@test_columnname+' like ''%'+@SearchString+'%'' THEN 1 ELSE 0 END) FROM ' +@test_tableschema +'.'+@test_table +' (nolock)'
EXEC sp_executesql @Flag_Query , N'@found_flag int out', @found_output out
IF @found_output>0
BEGIN
INSERT INTO @Final_Result (TableName, ColName,Found_Flag, QuickQuery)
VALUES(@test_table,@test_columnname,@found_output, 'SELECT DISTINCT '+@test_columnname+ ' FROM '+@test_tableschema +'.'+@test_table+' (nolock)')
END
DELETE FROM @Col_list WHERE RID = @ROWID
SET @ROWID = @ROWID + 1
END
/*--- Section 6: Listing all the TableName, ColumnName found with data and a Select statement for seeing all the distinct values in that column
where search result were found. ---*/
SELECT * FROM @Final_Result;
|
BI Stuff 