Search application through all database

Applications Search
#1

Hi everyone,

I want to create an application or a module that would allow to make researches through all tables of my database.
Please does somebody has any idea?

Thanks in advance!!!

#2

Yes, code it yourself …

#3

:smiley: I can’t i’m not a developper just a networking technician. A girl doesn’t code. hihi

Does somebody else has other ideas please!!!

#4

I think you can create a view for that. and then create a app for your searchs.

#5

Okey i will look for that!!

Thanks!!

#6

I have found something that seems to be a solution unfortunately there is an error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@NomTable sysname = NULL,
@ChaineCherchee varchar(500))
AS

DECLARE @NomC' at line 1

Do you have any idea please???

"CREATE PROCEDURE spTrouveChaineDsColonnes (@NomTable sysname = NULL,
@ChaineCherchee varchar(500))
AS

DECLARE @NomColonne nvarchar(128),
	@TypeDonnee nvarchar(128),
	@lig smallint,
	@NbLig int,
	@sql nvarchar(1000)

DECLARE Curseur_Recherche CURSOR FAST_FORWARD FOR 
	SELECT [name]
	FROM SYSOBJECTS 
	WHERE (
			OBJECTPROPERTY(id, N'IsMsShipped') = 0
			AND OBJECTPROPERTY(id, N'IsUserTable') = 1
		) AND (@NomTable IS NULL OR [name] = @NomTable)
	ORDER BY [name]

OPEN Curseur_Recherche

FETCH NEXT FROM Curseur_Recherche 
INTO @NomTable

SET @ChaineCherchee = '%' + @ChaineCherchee + '%'

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @lig = 1

	SELECT @NbLig = MAX([ORDINAL_POSITION])
	FROM [INFORMATION_SCHEMA].[COLUMNS]
	WHERE [TABLE_NAME] = @NomTable 
	GROUP BY [ORDINAL_POSITION]

	WHILE @lig <= @NbLig
	BEGIN 
		SELECT @NomColonne = '[' + [COLUMN_NAME] + ']',
			@TypeDonnee = [DATA_TYPE]
		FROM [INFORMATION_SCHEMA].[COLUMNS]
		WHERE [TABLE_NAME] = @NomTable 
			AND [ORDINAL_POSITION] = @lig
		ORDER BY [ORDINAL_POSITION]

		SET @lig = @lig + 1  

		SET @sql = NULL

		IF @TypeDonnee IN ( N'char', N'varchar', N'text')
			SET @sql = 'SELECT * FROM ' + @NomTable + ' WHERE PATINDEX(''' + @ChaineCherchee + ''', ' + @NomColonne + ') > 0'
		ELSE IF @TypeDonnee IN (N'nchar', N'nvarchar', N'ntext')
			SET @sql = 'SELECT * FROM ' + @NomTable + ' WHERE PATINDEX(''' + @ChaineCherchee + ''', CAST(' + @NomColonne + ' As TEXT)) > 0'

		IF @sql IS NOT NULL
		BEGIN
			SET @sql = 'IF EXISTS(' + @sql + ') PRINT ''[' + @NomTable + '].' + @NomColonne + ''''
			--PRINT (@sql)
			EXEC (@sql)
		END
	END

	FETCH NEXT FROM Curseur_Recherche 
	INTO @NomTable
END

CLOSE Curseur_Recherche
DEALLOCATE Curseur_Recherche

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"