SUCHEN IN EINER DATEBANK

Manchmal steht man vor der Aufgabe entweder eine bestimmte Spalte bzw. einen bestimmten Spalteninhalt zu finden. So wurde ich kürzlich gefragt, warum in einer Applikation ein Datensatz nicht gelöscht werden kann. Als Fehlermeldung erhielt der Kunde, dass der Datensatz noch in einer anderen Tabelle referenziert werden würde. Anhand der Applikation konnte man nicht ermitteln in wo der Datensatz referenziert wird. Somit muss man sich auf der Datenbankebene auf die Suche nach der Tabelle mit den zu löschenden Datensatz machen und dann herausfinden, wo der Datensatz referenziert wird.

Suche nach einer bestimmten Spalte

In der ersten Ausbaustufe könnte man sich auf die Suche nach einer Spaltenbezeichnung machen, die den entsprechenden Inhalt vermuten lässt. So könnte man “Sehr geehrter Herr” in der Spalte “Anrede” vermuten.

-- =============================================
-- Author:	Erhard Rainer
-- Create date: 2014-12-13
-- Description:	Search for a certain columnname
-- =============================================
-- exec [dbo].[usp_SearchColumn] @columnname = 'class' , @exact = 1
-- exec [dbo].[usp_SearchColumn] @columnname = 'class' , @exact = 1, @type = 'int'
ALTER PROCEDURE [dbo].[usp_SearchColumn]
	 @columnName as nvarchar(MAX) = null,
	 @exact as bit = 1,
	 @type as nvarchar(100) = null
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	Select 
		OBJECT_SCHEMA_NAME(o.object_id) as [Schema], 
		OBJECT_NAME(o.object_id) as [ObjectName],
		c.name as [Column],  
		st.name as [columnType]
		from sys.objects as o
		inner join sys.columns as c
		on o.object_id = c.object_id
		inner join sys.systypes as st
		on st.xtype = c.system_type_id
		Where 
			((@exact= 0 and c.name like '%' + @columnName +'%') or 
			(@exact=1 and c.name = @columnName))
		and 
			((@type is not null and st.name like '%' + @type + '%') or 
			(@type is null))
END
GO

Suche nach Inhalt einer bestimmten Spalte

Während die erste Methode nur nach einer Spalte namens “Anrede” sucht, könnte man das auch mit der Inhaltssuche kombinieren. dh. Suche “Sehr geehrter Herr” in allen Spalten, die Anrede lauten.

-- =============================================
-- Author:		Erhard Rainer
-- Create date: 2021-10-15
-- Description:	Search for a certain text in a certain column
-- =============================================
/*
EXECUTE [dbo].[usp_SearchColumnContent] @DataToFind = 'Alfred', 
	@ExactMatch = 0
EXECUTE [dbo].[usp_SearchColumnContent] @DataToFind = 'Alfreds Futterkiste', 
	@ExactMatch = 1
EXECUTE [dbo].[usp_SearchColumnContent] @DataToFind = 'Alfred', 
	@ExactMatch = 0, @ColumnName = 'CompanyName'
EXECUTE [dbo].[usp_SearchColumnContent] @DataToFind = 'Alfreds Futterkiste', 
	@ExactMatch = 1, @ColumnName = 'CompanyName'
EXECUTE [dbo].[usp_SearchColumnContent] @DataToFind = '57', 
	@ExactMatch = 0, @ColumnName = 'Address', @exactColumnName = 0
EXECUTE [dbo].[usp_SearchColumnContent] @DataToFind = '57', 
	@ExactMatch = 0, @type = 'int'
EXECUTE [dbo].[usp_SearchColumnContent] @DataToFind = 'Alfreds Futterkiste', 
	@ExactMatch = 0, @ColumnName = null
EXECUTE [dbo].[usp_SearchColumnContent] @DataToFind = 'Alfreds Futterkiste', 
	@ExactMatch = 0, @ColumnName = null, @debug = 1
*/
alter PROCEDURE [dbo].[usp_SearchColumnContent]
    @DataToFind NVARCHAR(MAX) = null,
    @ExactMatch BIT = 0,
	@ColumnName as nvarchar(MAX) = null,
	@exactColumnName as bit = 1,
	@type as nvarchar(100) = null,
	@output as bit = 1,
	@debug as bit = 0
AS
SET NOCOUNT ON

DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), RowFoundID int, SchemaName sysname, 
	TableName sysname, ColumnName SysName, DataType VARCHAR(100), 
	DataFound BIT, [RowCount] int)

INSERT  INTO @Temp(SchemaName,TableName, ColumnName, DataType)
Select 
	OBJECT_SCHEMA_NAME(o.object_id) as [Schema], 
	OBJECT_NAME(o.object_id) as [ObjectName],
	c.name as [Column],  
	st.name as [columnType]
	from sys.objects as o
	inner join sys.columns as c
	on o.object_id = c.object_id
	inner join sys.systypes as st
	on st.xtype = c.system_type_id
	Where 
		((@exactColumnName= 0 and c.name like '%' + @columnName +'%') or 
		(@exactColumnName=1 and c.name = @columnName) or 
		(@ColumnName is null))
	and 
		((@type is not null and st.name like '%' + @type + '%') or 
		(@type is null and st.name in 
		('ntext','text','nvarchar','nchar','varchar','char')))
	and 
			OBJECT_SCHEMA_NAME(o.object_id) <> 'sys'

if @debug = 1 
Begin
	Select * from @Temp
End

DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @RC as int
Set @i = 1
Set @MAX = (Select MAX(RowId) FROM  @Temp)

WHILE @i <= @MAX
    BEGIN
		Set @SchemaName =(Select SchemaName from @Temp where RowId = @i)
		Set @TableName =(Select TableName from @Temp where RowId = @i)
		Set @ColName =(Select ColumnName from @Temp where RowId = @i)
		if (@ExactMatch = 1)
		Begin 
			Set @SQL = 'Select @RC = Count(1) from ' + QUOTENAME(@SchemaName) + '.' 
			+ QUOTENAME(@TableName) + ' where Convert(nVarChar(4000), ' 
			+ QUOTENAME(@ColName) + ') = ''' + @DataToFind + '''' 
		end 
		else 
		begin 
			Set @SQL = 'Select @RC = Count(1) from ' + QUOTENAME(@SchemaName) + '.' 
			+ QUOTENAME(@TableName) + ' where Convert(nVarChar(4000), ' + 
			QUOTENAME(@ColName) + ') Like ''%' + @DataToFind + '%'''
		End

        if @debug = 1 PRINT @SQL
		EXECUTE sp_executeSQL @SQL, N'@RC INT OUTPUT', @RC OUTPUT

        IF @RC > 0 UPDATE @Temp SET DataFound = 1, [RowCount] = @RC WHERE RowId = @i

        SET @i = @i + 1
    END


if (@output = 1)
begin
	;with CTE as (
	Select ROW_NUMBER() over (order by [RowID] ASC) as RN,* from @Temp
	Where DataFound = 1
	)
	Update t
	Set T.RowFoundID = s.RN
	from @Temp as t 
	inner join CTE as s
	on t.RowId = s.RowId
	Select * from @Temp where DataFound = 1 order by  RowFoundID

	Set @i = 1
	Set @MAX = (Select MAX(RowFoundID) FROM  @Temp where DataFound = 1)

	if (@debug = 1)
	Begin
		Select * FROM  @Temp where DataFound = 1
	end

	WHILE @i <= @MAX
	begin
		Set @SchemaName =(Select SchemaName from @Temp where RowFoundID = @i)
		Set @TableName =(Select TableName from @Temp where RowFoundID = @i)
		Set @ColName =(Select ColumnName from @Temp where RowFoundID = @i)
		if (@ExactMatch = 1)
		Begin 
			Set @SQL = 'Select * from ' + QUOTENAME(@SchemaName) + '.' 
			+ QUOTENAME(@TableName) + ' where ' 
			+ QUOTENAME(@ColName) + ' = ''' + @DataToFind + '''' 
		end 
		else 
		begin 
			Set @SQL = 'Select * from ' + QUOTENAME(@SchemaName) + '.' 
			+ QUOTENAME(@TableName) + ' where ' 
			+ QUOTENAME(@ColName) + ' Like ''%' + @DataToFind + '%'''
		End
		if @debug = 1 PRINT @SQL
		exec(@SQL)
		Set @i = @i + 1
	end 
end 
else
Begin 
	SELECT  RowId,SchemaName,TableName, ColumnName, DataFound, [RowCount]
	FROM    @Temp
	WHERE   DataFound = 1
end
GO

Anbei ein paar Beispiele

• Finde in jeder beliebigen Spalte den genauen Begriff “Alfreds Futterkiste”
  EXECUTE [dbo].[usp_SearchColumnContent] @DataToFind = ‘Alfreds Futterkiste’, @ExactMatch = 1
• Finde in jeder beliebigen Spalte den ungefähren Begriff “Alfred”
  EXECUTE [dbo].[usp_SearchColumnContent] @DataToFind = ‘Alfred’, @ExactMatch = 0
• Finde in der genauen Spalte “CompanyName” den ungefähren Begriff “Alfred”
  EXECUTE [dbo].[usp_SearchColumnContent] @DataToFind = ‘Alfred’, @ExactMatch = 0, @ColumnName = ‘CompanyName’
• Finde in der genauen Spalte “CompayName” den genauen Begriff “Alfreds Futterkiste”
  EXECUTE [dbo].[usp_SearchColumnContent] @DataToFind = ‘Alfreds Futterkiste’, @ExactMatch = 1, @ColumnName = ‘CompanyName’
• Suche in allen Integer Spalten nach der Zahl 57
  EXECUTE [dbo].[usp_SearchColumnContent] @DataToFind = ’57’, @ExactMatch = 0, @type = ‘int’
• Suche in allen “Address” Spalten (auch ShipAddress usw.) nach der Hausnummer 57
  EXECUTE [dbo].[usp_SearchColumnContent] @DataToFind = ’57’, @ExactMatch = 0, @ColumnName = ‘Address’, @exactColumnName = 0
• uva.

Mit dem Zusatzparameter @debug = 1 kann man sich die dahinterliegenden Abfragen ausgeben lassen.

Proprietäre Software

Neben reinen SQL Lösungen, kann man auch proprietäre Software installieren, die genau die selbe Funktion umfassen. Ich persönlich finden diesbezüglich Redgate SQL Search sehr gut und eine brauchbare Alternative zu oben angeführten Script.

Links & Downloads

• usp_SearchColumn.sql
• usp_SearchColumnContent.sql