1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65
|
-- Renseigner une valeur Chaîne dans StringToFind si nécessaire ou '' sinon
-- Insert String Value <xxx without %> into StringToFind if necessary or ''
DECLARE @StringToFind VARCHAR(MAX)='$(RSTRING)'
-- Renseigner une valeur Date <AAAAMMJJ> dans DateToFind si nécessaire ou '' sinon
-- Insert Date Value <YYYYMMDD> into DateToFind if necessary or ''
DECLARE @DateToFind DATETIME='$(RDATE)'
DECLARE @StringNawak VARCHAR(MAX)='���'
DECLARE @DateNawak DATETIME='39991231'
DECLARE @TabMax INT=0
DECLARE @ColMax INT=0
DECLARE @i INT=1
DECLARE @NomTbl VARCHAR(100)
DECLARE @NomCol VARCHAR(100)
DECLARE @ReqSql VARCHAR(4000)
DECLARE @NbOccur INT=0
IF OBJECT_ID('tempdb..#TbCol') IS NOT NULL DROP TABLE #TbCol;
SET NOCOUNT ON
IF @StringToFind='' OR @StringToFind IS NULL
BEGIN
SET @StringToFind=@StringNawak
END
IF @DateToFind='' OR @DateToFind IS NULL
BEGIN
SET @DateToFind=@DateNawak
END
PRINT ('Chaîne Cible;'+@StringToFind)
PRINT ('Date Cible;'+CONVERT(VARCHAR(10),@DateToFind, 112))
IF @StringToFind=@StringNawak AND @DateToFind=@DateNawak
BEGIN
PRINT('Search Anything in All : Life is too short for that ...')
PRINT('Chercher n''importe quoi dans tout : la vie est trop courte pour s''y risquer ...')
END
ELSE
BEGIN
SELECT ROW_NUMBER() OVER(ORDER BY ISC.TABLE_NAME, ISC.COLUMN_NAME) AS Rang, CONCAT(ISC.TABLE_SCHEMA, '.', ISC.TABLE_NAME) AS NomTable, ISC.COLUMN_NAME AS NomCol,NULL AS NbrOk
INTO #TbCol
FROM INFORMATION_SCHEMA.COLUMNS AS ISC
WHERE DATA_TYPE IN ('varchar', 'char', 'nvarchar','date', 'datetime')
SET @TabMax=(SELECT COUNT(DISTINCT NomTable) FROM #TbCol)
SET @ColMax=(SELECT COUNT(*) FROM #TbCol)
PRINT (CONCAT('Nbr Tables', ';', @TabMax))
PRINT (CONCAT('Nbr Colonnes', ';', @ColMax))
WHILE @i<=@ColMax
BEGIN
SET @NomTbl=(SELECT NomTable FROM #TbCol WHERE Rang=@i)
SET @NomCol=(SELECT NomCol FROM #TbCol WHERE Rang=@i)
SET @ReqSql=CONCAT('SELECT COUNT(*) FROM ', @NomTbl, ' WHERE ', @NomCol, ' LIKE ''%',@StringToFind,'%''', ' OR ', @NomCol, '=''',CONVERT(VARCHAR(10),@DateToFind, 112),'''')
SET @ReqSql=CONCAT('UPDATE #TbCol SET NbrOk=(',@ReqSql,') WHERE Rang=',@i)
EXEC(@ReqSql)
SET @i=@i+1
END
SET @ColMax=(SELECT COUNT(*) FROM #TbCol)
SELECT * FROM #TbCol where NbrOk>0
DROP TABLE #TbCol
END |
Partager