搜索存储过程返回截止列数据

debugcn 发表于 Dev

阿尔玛

我有一个存储过程，该过程搜索数据库中的所有表并返回具有搜索值的表。

我的问题是我要搜索的值是表列之一的内容中XML内的值。

当该存储过程返回XML时，XML被切断，直到XML中间为止。我如何更改存储过程以从该列返回所有XML。

这是我的存储过程。

ALTER PROC [dbo].[SearchAllTables]
   (@SearchStr nvarchar(100))
AS
BEGIN
    DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(max))

    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 * FROM @Results
END

用户名

如何使用实际上提供查询的搜索技术，您可以通过它使用这些列，这就是我的工作方式，希望对您有所帮助

ALTER PROCEDURE SP_SearchDBForValue 
    @search_string  VARCHAR(100)
AS
BEGIN

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SearchResultTableColumn')
    CREATE TABLE SearchResultTableColumn (SearchStr VARCHAR(101), RecCount SMALLINT, TableName VARCHAR(100), ColumnName VARCHAR(100), CreateDate DATETIME Default GETDATE())
ELSE
    DELETE FROM SearchResultTableColumn 

DECLARE 
@table_name     SYSNAME, 
@table_id       INT,
@column_name    SYSNAME,
@sql_string     VARCHAR(2000)

DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239, 35)

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        IF @table_name <> 'SearchResultTableColumn' 
        BEGIN
          SET @sql_string = 'DECLARE @RecCount SMALLINT '+
              'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE ' + @column_name + ' LIKE ''%' + @search_string + '%'') '+
                'BEGIN SELECT @RecCount = COUNT(*) FROM ' + @table_name + ' WHERE ' + @column_name + ' LIKE ''%' + @search_string + '%'' '+
                'INSERT INTO SearchResultTableColumn VALUES( '''+@search_string+''', @RecCount,''' + @table_name + ''', ''' + @column_name + ''',GETDATE()) END '

            EXECUTE(@sql_string)
        END
        FETCH NEXT FROM columns_cur INTO @column_name
    END

    CLOSE columns_cur

    DEALLOCATE columns_cur

    FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur

DEALLOCATE tables_cur
END;
GO

DECLARE @SearchStr VARCHAR (400)
BEGIN
 SET @SearchStr = 'A1000'
 EXECUTE SP_SearchDBForValue @SearchStr
 SELECT RecCount, 'SELECT '+ColumnName+' ColName, * FROM '+TableName+ ' WHERE  '+ColumnName+' LIKE ''%' +SearchStr+ '%''' FROM SearchResultTableColumn
END
GO