{"id":632,"date":"2021-02-25T04:35:05","date_gmt":"2021-02-25T04:35:05","guid":{"rendered":"https:\/\/info.juliusgoh.life\/?p=632"},"modified":"2021-02-25T04:35:30","modified_gmt":"2021-02-25T04:35:30","slug":"mssql-search-through-whole-database-for-a-certain-string","status":"publish","type":"post","link":"https:\/\/info.juliusgoh.life\/?p=632","title":{"rendered":"MSSQL search through whole database for a certain string."},"content":{"rendered":"<pre>\r\n\/* Reto Egeter, fullparam.wordpress.com *\/\r\n\r\nDECLARE    @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int\r\nSET @SearchStrColumnValue = 'P042015003648' \/* use LIKE syntax P102004000005 P062005001955 P042011001733 P012009008972*\/\r\nSET @FullRowResult = 1\r\nSET @FullRowResultRows = 20\r\nSET @SearchStrTableName = NULL \/* NULL for all tables, uses LIKE syntax *\/\r\nSET @SearchStrColumnName = NULL \/* NULL for all columns, uses LIKE syntax *\/\r\nSET @SearchStrInXML = 0 \/* Searching XML data may be slow *\/\r\n\r\nIF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results\r\nCREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))\r\n\r\nSET NOCOUNT ON\r\n\r\nDECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)\r\nSET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')\r\nDECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))\r\n\r\nWHILE @TableName IS NOT NULL\r\nBEGIN\r\n    SET @TableName = \r\n    (\r\n        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))\r\n        FROM INFORMATION_SCHEMA.TABLES\r\n        WHERE TABLE_TYPE = 'BASE TABLE'\r\n            AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)\r\n            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName\r\n            AND    OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0\r\n    )\r\n    IF @TableName IS NOT NULL\r\n    BEGIN\r\n        DECLARE @sql VARCHAR(MAX)\r\n        SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE\r\n                FROM INFORMATION_SCHEMA.COLUMNS\r\n                WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2)\r\n                    AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1)\r\n                    AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')\r\n                AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END  + ',COLUMN_NAME)'\r\n        INSERT INTO @ColumnNameTable\r\n        EXEC (@sql)\r\n        WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)\r\n        BEGIN\r\n            PRINT @ColumnName\r\n            SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable\r\n            SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' \r\n            WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''\r\n            ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + ''' \r\n                  FROM ' + @TableName + ' (NOLOCK) ' +\r\n                ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' \r\n                    WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'\r\n                    ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue\r\n            INSERT INTO #Results\r\n            EXEC(@sql)\r\n            IF @@ROWCOUNT > 0 IF @FullRowResult = 1 \r\n            BEGIN\r\n                SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' +\r\n                          ' FROM ' + @TableName + ' (NOLOCK) ' +\r\n                          ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' \r\n                    WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'\r\n                    ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue\r\n                EXEC(@sql)\r\n            END\r\n            DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName\r\n        END    \r\n    END\r\nEND\r\nSET NOCOUNT OFF\r\n\r\nSELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results\r\nGROUP BY TableName, ColumnName, ColumnValue, ColumnType\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\/* Reto Egeter, fullparam.wordpress.com *\/ DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int SET @SearchStrColumnValue = &#8216;P042015003648&#8217; \/* use LIKE syntax P102004000005 P062005001955 P042011001733 P012009008972*\/ SET @FullRowResult = 1 SET @FullRowResultRows = 20 SET @SearchStrTableName = NULL \/* NULL for all tables, uses LIKE syntax *\/ SET @SearchStrColumnName = NULL [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":634,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"image","meta":{"footnotes":""},"categories":[25],"tags":[],"_links":{"self":[{"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/posts\/632"}],"collection":[{"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=632"}],"version-history":[{"count":1,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/posts\/632\/revisions"}],"predecessor-version":[{"id":635,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/posts\/632\/revisions\/635"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=\/wp\/v2\/media\/634"}],"wp:attachment":[{"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=632"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=632"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/info.juliusgoh.life\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=632"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}