Superdopey’s Techblog

Als je niet weet in welke tabel een bepaalde tekst string in een SQL database staat dan kan de volgende stored procedure je verder helpen.

Deze zoekt namelijk op basis van een zoek string parameter heel je database door op zoek naar deze string en toon in welke tabellen deze voorkomt.

Deze credits voor deze stored procedure gaan naar Narayana Vyas Kondreddi

CREATE PROC SearchAllTables
(
	@SearchStr nvarchar(100)
)
AS
BEGIN

	-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
	-- Purpose: To search all columns of all tables for a given search string
	-- Written by: Narayana Vyas Kondreddi
	-- Site: http://vyaskn.tripod.com
	-- Tested on: SQL Server 7.0 and SQL Server 2000
	-- Date modified: 28th July 2002 22:50 GMT

	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

Het verwijderen van dubbele rijen is vaak een lastige taak. Met de onderstaande query is dit een stuk makkelijker, namelijk door eerst in een subselect query de unieke rijen op te halen via de GROUP BY kunnen vervolgens de overige rijen worden (die niet voorkomen in deze subselect ) worden verwijdert :

DELETE FROM eenTabel
WHERE 	IDcolumn NOT IN
	(SELECT MAX(IDcolumn)
	FROM eenTabel
	GROUP BY columnMetDubbeleWaarde, columnMetDubbeleWaarde, columnMetDubbeleWaarde)

Paginering in TSQL is wat lastiger dan bij MySQL waar er gebruik gemaakt kan worden van LIMI, TSQL mist deze opdracht maar hier is een workaround.

To filter a table with one query you can do the following:

EmployeeID int
DepartmentID int
ManagerID int
LocationID int

Now assume you want users of your application to be able to find an Employee by specifying any of the criteria they so desire. User1 may just want to find all employees who Joe Manager [ManagerID: 5] manages, User2 may want to find all employees who Joe Manager manages, but only if they are in the Technology Department [DepartmentID: 49]. Using the technique I shared with my co-worker would result in a proc that looks like this:

CREATE PROCEDURE ap_SearchEmployee
(
@EmployeeID int = -1,
@DepartmentID int = -1,
@ManagerID int = -1,
@LocationID int = -1
)

as

SET NOCOUNT ON

SELECT * FROM Employee e
WHERE
(@EmployeeID = -1 OR e.EmployeeID = @EmployeeID)
AND (@DepartmentID = -1 OR e.DepartmentID = @DepartmentID)
AND (@ManagerID = -1 OR e.ManagerID = @ManagerID)
AND (@LocationID = -1 OR e.LocationID = @LocationID)

The where clause checks each parameter to see if it is -1 which means we don’t want to filter our results by that parameter. If the parameter is not -1 it then checks the fields value with that of the parameter. By using this technique my co-worker was able to remove the duplicate logic and nasty if/else blocks within his stored procedure which provided him with a much cleaner solution!

source