动态搜索给定表的列 [英] Dynamically search columns for given table

问题描述

我需要为正在构建的Java应用程序创建搜索，用户可以在其中基于他们当前正在查看的表和他们提供的搜索字词在SQL数据库中进行搜索。最初，我要做的是这样简单的事情：

  SELECT * FROM<表名>在哪里（（从INFORMATION_SCHEMA.COLUMNS中选择SELECT COLUMN_NAME 
，其中TABLE_NAME ='<表名>'）
作为VARCHAR）喜欢'％<搜索词>％'
  

，但是该子查询返回一个以上的结果，因此我尝试创建一个过程以遍历给定表中的所有列并放入结果表中的任何相关字段，例如：

  CREATE PROC sp_search 
 @tblname VARCHAR（4000），
 @term VARCHAR（4000）
 AS 
在
上设置nocount选择COLUMN_NAME 
进入#tempcolumns 
从INFORMATION_SCHEMA.COLUMNS TABLE_NAME = @tblname 
 
 ALTER TABLE #tempcolumns 
添加打印的BIT，
 num SMALLINT IDENTITY 
 
更新#tempcolumns 
 SET打印= 0 
 
宣告@colname VARCHAR（4000），
 @num SMALLINT 
存在时（从#tempcolumns中选择MIN（num）打印= 0）
开始
 SEL ECT @num = MIN（num）
从#tempcolumns 
打印= 0 
 SELECT @colname = COLUMN_NAME 
 FROM #tempcolumns 
 WHERE num = @num 
 SELECT * INTO #results from @tblname WHERE CAST（@colname AS VARCHAR）
 Like'％'+ @term +'％'-这就是我遇到麻烦的地方
 UPDATE #tempcolumns 
 SET print = 1 
 WHERE @num = num 
 
 END 
 
 SELECT * FROM #results 
 GO 
  

这有两个问题：首先是它被卡在无限循环中，其次我无法从@中选择任何东西tblname。我也尝试过使用动态SQL，但是我不知道如何从中获得结果，或者甚至不可能。

这是我正在做的作业在大学里，经过数小时的努力，我已经走了很远。有什么方法可以做我想做的事吗？

解决方案

1. 您只需要搜索列实际上包含字符串，而不是表中的所有列（可能包括整数，日期，GUID等）。


2. 您不需要#temp表（当然也不需要＃


3. 您需要使用动态SQL（尽管我不确定到目前为止这是否已经成为您课程的一部分）。


4. 我发现遵循一些建议是有益的简单约定，您违反了所有约定：
   
   
   
   • 使用过程 c而不是 PROC -这不是麻烦，这是一个存储过程。
   
   
   • 在引用任何对象时使用 dbo。（或替代模式）前缀。
   
   
   • 将过程主体包装在 BEGIN / END 中。
   
   
   • 自由地使用元音。您是否节省了那么多按键操作，不用担心时间，说 @tblname 而不是 @tablename 或 @table_name ？我不是为特定的约定而战，而是以牺牲可读性为代价来保存字符在70年代失去了魅力。
   
   
   • 请勿使用 sp _ 存储过程的前缀-该前缀在SQL Server中具有特殊含义。将该过程命名为要执行的操作。它不需要前缀，就像我们知道它们是表一样，即使没有 tbl 前缀也是如此。如果确实需要前缀，请使用其他前缀，例如 usp _ 或 proc _ ，但我个人并不认为该前缀为您提供您尚未拥有的任何信息。
   
   
   • 由于表是使用Unicode存储的（某些列可能也是），因此参数应为 NVARCHAR ，而不是 VARCHAR 。并且标识符的上限为128个字符，因此没有理由支持> @tablename 的257个字符。
   
   
   • 以分号结束语句。
   
   
   • 使用目录视图代替 INFORMATION_SCHEMA -尽管后者是您的教授可能会教过的并且可能会期望的。
   
   
   








 创建过程dbo.SearchTable 
 @tablename NVARCHAR（257），
 @term NVARCHAR（4000）
 AS 
开始
开启NOCOUNT； 
 
声明@sql NVARCHAR（MAX）; 
 
 SET @sql = N’SELECT * FROM’+ @tablename +‘WHERE 1 = 0’； 
 
 SELECT @sql = @sql +'
 OR'+ c.name +'喜欢'％'+ REPLACE（@term，``''，'''''' ）+'％'''
来自
 sys.all_columns AS c 
内部联接
 sys.types AS t 
 ON c.system_type_id = t.system_type_id 
 AND c.user_type_id = t.user_type_id 
 WHERE 
 c。[object_id] = OBJECT_ID（@tablename）
 AND t.name IN（N'sysname'，N'char'， N'nchar'，
 N'varchar'，N'nvarchar'，N'text'，N'ntext'）; 
 
 PRINT @sql; 
 
-EXEC sp_executesql @sql; 
 END 
 GO 
  

当您很高兴它输出选择所需的查询，注释掉打印，并取消注释 EXEC 。 / p>


I need to create a search for a java app I'm building where users can search through a SQL database based on the table they're currently viewing and a search term they provide. At first I was going to do something simple like this:

SELECT * FROM <table name> WHERE CAST((SELECT COLUMN_NAME 
  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table name>') 
  AS VARCHAR) LIKE '%<search term>%'

but that subquery returns more than one result, so then I tried to make a procedure to loop through all the columns in a given table and put any relevant fields in a results table, like this:

CREATE PROC sp_search
    @tblname VARCHAR(4000),
    @term VARCHAR(4000)
AS
    SET nocount on
    SELECT COLUMN_NAME 
    INTO #tempcolumns
    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tblname

    ALTER TABLE #tempcolumns
    ADD printed BIT,
        num SMALLINT IDENTITY

    UPDATE #tempcolumns
        SET printed = 0

    DECLARE @colname VARCHAR(4000),
        @num SMALLINT
    WHILE EXISTS(SELECT MIN(num) FROM #tempcolumns WHERE printed = 0)
    BEGIN
        SELECT @num = MIN(num) 
            FROM #tempcolumns 
            WHERE printed = 0
        SELECT @colname = COLUMN_NAME 
            FROM #tempcolumns 
            WHERE num = @num
        SELECT * INTO #results FROM @tblname WHERE CAST(@colname AS VARCHAR) 
                    LIKE '%' + @term + '%' --this is where I'm having trouble
        UPDATE #tempcolumns
            SET printed = 1
            WHERE @num = num

    END

    SELECT * FROM #results
GO

This has two problems: first is that it gets stuck in an infinite loop somehow, and second I can't select anything from @tblname. I tried using dynamic sql as well, but I don't know how to get results from that or if that's even possible.

This is for an assignment I'm doing at college and I've gotten this far after hours of trying to figure it out. Is there any way to do what I want to do?

解决方案

1. You need to only search columns that actually contain strings, not all columns in a table (which may include integers, dates, GUIDs, etc).
2. You shouldn't need a #temp table (and certainly not a ##temp table) at all.
3. You need to use dynamic SQL (though I'm not sure if this has been part of your curriculum so far).
4. I find it beneficial to follow a few simple conventions, all of which you've violated:
   • use PROCEDURE not PROC - it's not a "prock," it's a "stored procedure."
   • use dbo. (or alternate schema) prefix when referencing any object.
   • wrap your procedure body in BEGIN/END.
   • use vowels liberally. Are you saving that many keystrokes, never mind time, saying @tblname instead of @tablename or @table_name? I'm not fighting for a specific convention but saving characters at the cost of readability lost its charm in the 70s.
   • don't use the sp_ prefix for stored procedures - this prefix has special meaning in SQL Server. Name the procedure for what it does. It doesn't need a prefix, just like we know they're tables even without a tbl prefix. If you really need a prefix there, use another one like usp_ or proc_ but I personally don't feel that prefix gives you any information you don't already have.
   • since tables are stored using Unicode (and some of your columns might be too), your parameters should be NVARCHAR, not VARCHAR. And identifiers are capped at 128 characters, so there is no reason to support > 257 characters for @tablename.
   • terminate statements with semi-colons.
   • use the catalog views instead of INFORMATION_SCHEMA - though the latter is what your professor may have taught and might expect.

CREATE PROCEDURE dbo.SearchTable
    @tablename NVARCHAR(257),
    @term      NVARCHAR(4000)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);

    SET @sql = N'SELECT * FROM ' + @tablename + ' WHERE 1 = 0'; 

    SELECT @sql = @sql + ' 
      OR ' + c.name + ' LIKE ''%' + REPLACE(@term, '''', '''''') + '%'''
    FROM 
      sys.all_columns AS c
    INNER JOIN 
      sys.types AS t
      ON c.system_type_id = t.system_type_id
      AND c.user_type_id = t.user_type_id
    WHERE 
      c.[object_id] = OBJECT_ID(@tablename)
      AND t.name IN (N'sysname', N'char', N'nchar', 
        N'varchar', N'nvarchar', N'text', N'ntext');

    PRINT @sql;

    -- EXEC sp_executesql @sql;
END
GO

When you're happy that it's outputting the SELECT query you're after, comment out the PRINT and uncomment the EXEC.

这篇关于动态搜索给定表的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！