在 sql 数据库中搜索列名,然后在重新调整的列中搜索值 [英] Search sql database for a column name, then search for a value within the retuned columns
本文介绍了在 sql 数据库中搜索列名,然后在重新调整的列中搜索值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
此查询将在数据库中搜索特定列名.我想更进一步,在返回的列中搜索特定值.
This query will search a database for a specific column name. I would like to go one step further and search the returned columns for a specific value.
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name,
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Example%'
有什么想法吗?
非常感谢
推荐答案
不存在用于此类搜索的系统表.而您可以为此目的而尝试
There is not such system table present for this kind of searching. Whereas you can try this for your purpose
DECLARE @ValueToSearch NVARCHAR(500)
DECLARE @SearchColumn NVARCHAR(100)
DECLARE @TableName NVARCHAR(200)
DECLARE @ColumnName NVARCHAR(200)
SET @ValueToSearch ='YOUR VALUE TP SEARCH'
SET @SearchColumn = 'YOUR COLUMN'
DECLARE @getResult CURSOR
SET @getResult = CURSOR FOR
SELECT t.name AS table_name,c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name = @SearchColumn
OPEN @getResult
FETCH NEXT FROM @getResult INTO @TableName,@ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET NOCOUNT ON ;
DECLARE @RESULT INT;
DECLARE @TYPE INT
DECLARE @QUERY NVARCHAR(1000)
SET @QUERY = 'select @RESULT=count(*) from ' + ISNULL(@TableName,'') +' WHERE '+ ISNULL(@ColumnName,'')+'='''+ ISNULL(@ValueToSearch,'') +''''
EXEC sp_executesql @QUERY,
N'@result int OUTPUT, @type int OUTPUT',
@RESULT OUTPUT,
@TYPE OUTPUT
IF(ISNULL(@RESULT,0)>0)
BEGIN
SET NOCOUNT ON;
SELECT ' COLUMN '+ @ColumnName + ' OF TABLE ' +@TableName+ ' HAS THIS VALUE.'
END
FETCH NEXT FROM @getResult INTO @TableName,@ColumnName
END
CLOSE @getResult
DEALLOCATE @getResult
谢谢
马诺伊
这篇关于在 sql 数据库中搜索列名,然后在重新调整的列中搜索值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文