Skip to the content

Hoe kan ik zoeken in alle tabellen SQL Server

Er zijn ontwikkelaars die ervoor kiezen om een SQL Server database te ontwikkelen zonder een duidelijk relationeel verband. Op zich geen dramatisch probleem want dit is prima af te handelen in het datamodel van de applicatie, al komt het de performance van de database niet ten goede.

Zo ook bij een klant die om reden van performance door ons een nieuwe applicatie heeft laten bouwen. Bij de conversie van de oude database naar de nieuwe, waar veldnamen alles behalve consistent waren, was het zoeken naar relaties tussen de tabellen een crime.

Om te kunnen zoeken naar de locatie van data in de tabellen heb ik een bestaand script omgebouwd zodat we in alle tabellen kunnen zoeken naar een term. De procedure is niet snel maar wel erg handig voor het zoeken van willekeurige waarden in een SQL Server database.

De stored procedure is als volgt opgebouwd

/****** Object:  StoredProcedure [dbo].[SearchAllTables]    Script Date: 22-7-2015 20:40:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[SearchAllTables]
(
	@SearchStr nvarchar(100)
)
AS
BEGIN




	CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

	SET NOCOUNT ON

	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)

		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)
	
			IF @ColumnName IS NOT NULL
			BEGIN
				INSERT INTO #Results
				EXEC
				(
					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				)
			END
		END	
	END

	SELECT ColumnName, ColumnValue FROM #Results
END

GO

De SQL Server zoekopdracht uitgelegd


We maken eerst een tijdelijke tabel waarin het resultaat van de zoekopdracht wordt vastgelegd.

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

Vervolgens bepalen we in de declaratie van de parameters dat de tabelnaam een lege string is.

SET @TableName = ''

In de lus gebruiken we de lege string om de eerste tabelnaam te bepalen in het database schema. Immers in de where van het select statement moet de tabelnaam groter zijn dan de lege string.

WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

Omdat we ook de parameter @ColumName een lege string meegeven zal deze de veldnamen lussen uit de tabel. In het voorbeeld worden uitsluitend de veldtype char, varchar, nchar en nvarchar meegenomen in het resultaat.

SET @ColumnName = ''

Nu hebben we de tabelnaam en de veldnamen waarin we willen zoeken naar het resultaat in de sql-server database.

Tot slot gebruiken we een dynamische query om de select statement in op te bouwen, uit te voeren en het zoekresultaat op te slaan in de tijdelijke tabel.

'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

Na de lus geven we de inhoud terug van de tijdelijke tabel met daarin alle tabelnamen, velden en de veldwaarde waarin het zoekresultaat is gevonden.

Uitvoeren van het SQL Server zoekscript


Middels de onderstaande opdracht voer je de procedure uit waarin ik on onderstaande voorbeeld zoek naar de term 'reinder'.

EXEC [dbo].[SearchAllTables] 'REINDER'