Execute an sql statement at all database of an sql server instanct.

In this post I will write about a problem I had. I needed to find a value in a specific name of a client. I have many similar databases but the data in the databases differ. So, I needed to execute a SQL statement at every database to find the right value. After some searching I found the sp_msforeachtable system stored procedure. With this procedure you can execute SQL code at every database at you SQL server instance.

Here are the examples I needed to find the customer with the customerId 2 in all databases.

--select customerId in the customer table at every database at the sql server instance
DECLARE @command varchar(1000) 
SELECT @command = 'USE ? SELECT DB_NAME() as DBName, * FROM Customers where CustomerId = 2'; 
EXEC sp_MSforeachdb @command
GO

Here you see the result, I found it in two databases (master and Preparation).

After I solved that problem I tried some different things with the sp_MSforeachdb procedure. In the next example I select all databases with name, filepath and size of the file.

--select all databases with name, filename and size
DECLARE @command varchar(1000) 
SELECT @command = 'USE ? SELECT DB_NAME() as DBName, SF.Name as Name, 
SF.filename as Filename, SF.size as Size FROM sys.sysfiles SF'
EXEC sp_MSforeachdb @command
GO

Here you see the result of my SQL server instance.

After that i tried to select all tables in all databases that start with an p. As you see u can use the ? as an placeholder for the database name of the actual database.

--select all tables that start with p
DECLARE @command varchar(1000) 
SELECT @command = 'USE ? SELECT DB_NAME() as DBName, '' '' as Name, '' '' as xtype UNION 
SELECT DB_NAME() DBName, Name, xtype FROM SYSOBJECTS WHERE xtype = ''U'';'
EXEC sp_MSforeachdb @command
GO

Here you can see the result. All tables at all databases that start with a p.

Then I thought about what I could do with that procedure and while searching the internet for further good examples I discovered the sp_MSforeachtable procedure. That procedure does not execute the command for every database it executes the command for every table in the actual database. That means the ? is the placeholder for the tablename. Here are some examples what you can do with them.

--select the number of rows in every table 
EXEC sp_MSforeachtable 'Select ''?'' as tableName, 
count(1) numRecords from ?';

--space used of every table 
EXEC sp_MSforeachtable 'exec sp_spaceused [?]';


Two further idears to use the sp_MSforeachtable procedure are to rebuild the index of every table in the database and to update the statistics for all tables at the database.

--update all statistics in all Tables in a database
EXEC sp_MSforeachtable 'update statistics ? with all';

--rebuild all indexes for all tables in a database
EXEC sp_MSforeachtable 'print ''?'' DBCC DBREINDEX(''?'','''',80)';
Advertisements

Calculating string difference values with the Levenshtein fuzzy string algorithm (part 2: implementation in t-sql).

In this second post i will write about the Levenshtein algorithm and how to implement it in t-sql (in part 1 i have explained the implementation in c#). With this algorithm it is possible to compare two strings and calculate the a difference value of them. Lets asume that we have a table in a database with words in it.

Now if the user overtakes a word we want to suggest the words out of the table words that is the closest. So we need the Levenshtein algorithm to calculate the levenshtein difference between the overtaken word and all words in the table words. Then we take the words with the minimal levenshtein distance and give it the user to choose out of that words if he wants. For example if the user overtakes ‘lay’ the words with the minimal levenshtein distance are ‘lady’ and ‘lacy’. So this are the words we would suggest to the user. To acomplish that we need a function that overtakes the source and the target string and calculates the levenshtein distance.

CREATE FUNCTION dbo.CalculateLevenshteinValue(@source nvarchar(max), @target nvarchar(max))
RETURNS int
BEGIN  
     declare @sourceLength int, @targetLength int

     set @sourceLength = len(@source);
     set @targetLength = len(@target);

     declare @array table ( i int, j int, value int);
     declare @i int, @j int, @result int

     set @i = 0;
     set @j = 0;

     WHILE @i <= @sourceLength
     BEGIN
	     insert @array( i, j, [value])
	     values( @i, 0, @i) 
  	     SET @i = @i + 1;
     END
     WHILE @j <= @targetLength
     BEGIN
	     insert @array ( i, j, [value])
	     values ( 0, @j, @j) 
  	     SET @j = @j + 1;
     END

     set @i = 1;
     set @j = 1;

     declare @equal int, @minInsert int, @minDelete int, 
	 declare @minInterchange int, @equalValue int, @insertValue int
     declare @sourceChar nvarchar(1), @targetChar nvarchar(1)

     WHILE @i <= @sourceLength
     BEGIN
	     WHILE @j <= @targetLength
	     BEGIN
		     SET @sourceChar = substring(@source, @i, 1);		
		     SET @targetChar = substring(@target, @j, 1);
		     SET @equal = 2;
		     if(@sourceChar = @targetChar)
		     begin
			     SET @equal = 0;	
		     end
		     select @minInsert = value + 1 
                     from @array a where a.i = @i - 1 and a.j = @j
		     select @minDelete = value + 1 
                     from @array a 
                     where a.i = @i and a.j = @j - 1
		     select @minInterchange = [value] + @equal 
                     from @array a 
                     where a.i = @i - 1 and a.j = @j - 1
		     select @insertValue = 
                     dbo.ScalarMinValue(dbo.ScalarMinValue(@minInsert, @minDelete), @minInterchange)
		     insert @array ( i, j, [value])
		     values ( @i, @j, @insertValue) 
	  	     SET @j = @j + 1;
	     END
	     SET @i = @i + 1;
	     SET @j = 1
     END
     select @result = [value] 
     from @array a 
     where a.i = @sourceLength and a.j = @targetLength
RETURN @result;
END;
CREATE FUNCTION dbo.ScalarMinValue
(@Value1 int, @Value2 int)
RETURNS int
BEGIN
	declare @return int	
	Select @return = Case When @Value1 < @Value2 Then @Value1 Else @Value2 End	
	return @return
END

The calculation is very simmular to the calculation in c# but because the sql server does not support arrays i am using a temptable to store the calculated values. With this function that calculates the difference between two words we can write the stored procedure that takes the source string and select the target strings out of the Words table with the smallest levenshtein distance.

CREATE procedure LevenshteinCalculator
@source nvarchar(256)
as
	select dbo.CalculateLevenshteinValue(@source,Word) Levenshtein, Word 
	from Words wout
	where dbo.CalculateLevenshteinValue(@source,Word) = (
	select MIN(m.Levenshtein)
	from
	(select dbo.CalculateLevenshteinValue(@source,Word) Levenshtein, Word 
	from Words w) m)
go

So if we execute the CalculateLevenshteinValue procedure with ‘lay’ we get the following result.

exec LevenshteinCalculator @source='lay'

So lacy and lady are very close to lay and so we could assume that the user has made typing error and suggest him the two words .
(part1) (part2)