在数据库中搜索文本，并且int返回错误多部分标识符...无法绑定 [英] Search a database for text and int is returning error The multi-part identifier ... could not be bound
问题描述
我试图修改和找到我发现的所有表脚本（在此答案中（从2009年开始）包含INT选项，但出现错误：
I have tried to modify and existing search all tables script that I found (in this answer from 2009) to include INT option, but I'm getting an error:
消息4104，级别16，状态1，第1行
不能绑定多部分标识符 dbo.ADACS_MAPS。
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.ADACS_MAPS" could not be bound.
运行时数据库中的每一列。
for every single column in the database when I run it.
ALTER PROC [dbo].[SearchAllTables]
(
@SearchStr nvarchar(100),
@SearchInt int = 0
)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))
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', 'int')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO @Results
EXEC
(
'if exists (SELECT name FROM SYSOBJECTS WHERE xtype = ''U'' and name like trim('']'' from SUBSTRING(' + @TableName + ' , 8, 100))) BEGIN '
+'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' = ' + @SearchInt
+' END
ELSE
BEGIN '
+
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
+' END'
)
END
END
END
SELECT ColumnName, ColumnValue FROM @Results
END
有人可以指出我的意思吗这样做是错误的，因为（我不敢承认）这是我所不知道的。
Can somebody please point out what I'm doing wrong, as this (I'm not ashamed to admit) is beyond my knowledge.
您应该可以在任何数据库上运行它，因此我没有提供演示数据。
You should be able to run this on any DB, so I have not included demo data.
编辑1：
我唯一修改的部分是：
- 添加了参数
- 更改了最终选择：
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
到现在。
编辑2：
找到了解决方案此处效果很好，但我将在脚本中进行操作，以查看其为何不起作用。
Found a solution here that works well, but I will work on my script to see why its not working.
多亏了前2个注释器的帮助，我将按照建议的选择更仔细地查看该代码。
Thanks to the first 2 commentators for the assist, I will look at that code a little more closely using the select as suggested.
推荐答案
当我调试/开发动态代码时，我倾向于使用print语句查看我的代码出了问题的地方。我中断了该过程，然后打印了包含动态sql的变量。您还可以使用调试器。
When i am debugging/developing dynamic code i tend to use the print statement to see where my code goes wrong. I break away the procedure and then print the variable containing the dynamic sql. You could also use the debugger.
发生的事情是，在动态代码中，您正在搜索不存在的列。发生这种情况是因为您缺少多余的引号来转换为字符串。当我在Northwind数据库上使用您的过程并搜索 cat时，下面是第一部分动态代码的打印。
Whats happening is that in your dynamic code you are searching for a column which does not exist. This happens because you are missing extra quotes to convert into a string. Below is a print of the first bit of dynamic code when i use your procedure on the Northwind database and search for 'cat'.
if exists (SELECT name
FROM SYSOBJECTS
WHERE xtype = 'U'
-- SQL server sees [dbo].[Categories] as a non existing column
and name like trim(']' from SUBSTRING([dbo].[Categories] , 8, 100)))
BEGIN
SELECT '[dbo].[Categories].[CategoryID]', LEFT([CategoryID], 3630)
FROM [dbo].[Categories] (NOLOCK) WHERE
[CategoryID] = 1 END
ELSE
BEGIN SELECT '[dbo].[Categories].[CategoryID]', LEFT([CategoryID], 3630)
FROM [dbo].[Categories] (NOLOCK) WHERE [CategoryID] LIKE '%cat%' END
下面是对代码的两项更改，以使其正常工作。因为要查找的表的名称列中没有该模式，所以多余的引号和一个多余的变量会剥夺该模式。
Below are two changes to your code to make it work. Extra quotes and an extra variable which strips away the schema since the schema is not present in the name column of the table you are looking in.
ALTER PROC [dbo].[SearchAllTables]
(
@SearchStr nvarchar(100),
@SearchInt int = 0
)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @TableNoSchema nvarchar(256)
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
)
-- Since the table used below in the dynamic SQL does not have a prepended schema is strip it here
SET @TableNoSchema = SUBSTRING(@TableName, CHARINDEX('.',@TableName) + 2, LEN(@TableName) - CHARINDEX('.',@TableName) -2 )
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', 'int')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO @Results
EXEC -- only added extra quotes so SQL server sees a string instead of a column
(
'if exists (SELECT name FROM SYSOBJECTS WHERE xtype = ''U'' and name like trim('']'' from SUBSTRING(''' + @TableNoSchema + ''' , 8, 100))) BEGIN '
+'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' = ' + @SearchInt
+' END
ELSE
BEGIN '
+
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
+' END'
)
END
END
END
SELECT ColumnName, ColumnValue FROM @Results
END
这篇关于在数据库中搜索文本，并且int返回错误多部分标识符...无法绑定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
