Kui enam meeles pole, mida kuhugi sai andmebaasi pandud siis otsimisel abiks järgnev skript:
set nocount on
DECLARE @pikkus INT, @rowID INT, @maxRowID INT, @sql NVARCHAR(4000), @searchValue NVARCHAR(100)
SET @searchValue = 'ei tööta' --seda otsitakse
DECLARE @statements TABLE (rowID INT, SQLL NVARCHAR(MAX) COLLATE DATABASE_DEFAULT)
CREATE TABLE #results (tableName NVARCHAR(250) COLLATE DATABASE_DEFAULT, tableSchema NVARCHAR(250) COLLATE DATABASE_DEFAULT
, columnName NVARCHAR(250) COLLATE DATABASE_DEFAULT, foundtext NVARCHAR(MAX) COLLATE DATABASE_DEFAULT )
SET @rowID = 1
SET @pikkus=LEN(@searchValue)
--TEXT 35
--NTEXT 99
--VARCHAR 167
--CHAR 175
--NVARCHAR, SYSNAME 231
--NCHAR 239
--XML 241
--create CTE table holding metadata
;WITH MyInfo (tableName, tableSchema, columnName, XTYPE) AS (
SELECT sysobjects.name AS tableName, USER_NAME(sysobjects.uid) AS tableSchema
, syscolumns.name AS columnName, syscolumns.XTYPE
FROM sysobjects WITH(NOLOCK) INNER JOIN syscolumns WITH(NOLOCK)
ON (sysobjects.id = syscolumns.id)
WHERE sysobjects.xtype = 'U' AND sysobjects.category=0
AND sysobjects.name <> 'sysdiagrams' --MSSQL diagramme ei vaata
AND syscolumns.XTYPE IN (35,99,167,175,231,239,214) AND syscolumns.prec >= @pikkus
)
INSERT INTO @statements
SELECT row_number() over (order by tableName, columnName) AS rowID, 'INSERT INTO #results SELECT '''+tableName+''', '''+tableSchema+''', '''+columnName+''', CAST('+columnName+' AS NVARCHAR(MAX)) FROM ['+tableSchema+'].['+tableName+'] WITH (NOLOCK) WHERE '+
CASE WHEN myInfo.XTYPE=241 --XML
THEN +'CONVERT(NVARCHAR(MAX),['+columnName+'])'
ELSE '['+columnName+']'
END+' LIKE ''%'+@searchValue+'%'''
FROM myInfo
SET @maxRowID = ( SELECT MAX(rowID) FROM @statements )
WHILE @rowID <= @maxRowID
BEGIN
SET @sql = (SELECT sqll FROM @statements WHERE rowID = @rowID )
EXEC sp_executeSQL @sql
SET @rowID = @rowID + 1
END
SELECT * FROM #results
drop table #results
