Category Archives: SQL

SQL

Delete full or partial data from MDS entity

I have started Microsoft MDS (Master Data Services) as a new category to share some really quick and helpful solution of problems faced while working. Let me know if you have Questions in comments section.
To delete partial (good number of rows) or full Entity data with MDS database, Manual UI usage is slow and really bad if numbers of rows are good enough.

Use following query and read the comments /* . . . */ to see where you can update, based on your situation to get things done.


DECLARE @DateTimestamp nvarchar(100) = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),GETDATE(),120),' ',''),'-',''),':',''); /*Can be used to have distinct Timestamp in BatchTag of stg entity data push*/

DECLARE @MDSVersion nvarchar(20) = 'VERSION_1';/*Specify latest version of MDS model*/
DECLARE @DeleteBatchTag nvarchar(100);


SET @DeleteBatchTag = 'Delete' +'Entity1' + @DateTimestamp ;
INSERT INTO [stg].[Entity_1_Leaf]
(ImportType, ImportStatus_ID, BatchTag, Code)
SELECT 6 AS ImportType,

/*Import Type 6 will help you to delete the rows even if Entity is referred in other child entities as Domain Based. Don't use 4 import type as its going to put you in viscous cycle of deactivated - activated domain based data.*/


0 AS ImportStatus_ID,
@Action_BatchTag
, Code
FROM mdm.Entity_1
/* Use WHERE clause on current data in entity to specify what you want to delete */
WHERE Name Like '%Test%';

EXECUTE [stg].[udp_Entity_1_Leaf] @VersionName = @Version , @LogFlag = 0, @BatchTag = @DeleteBatchTag , @Batch_ID = NULL;
Advertisements

Search a Text string in whole SQL Server database

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;