SQL Server 已知字段名和对应的一个值,如何查到这个表名?

发布网友 发布时间：2022-04-15 01:50

我来回答

共7个回答

热心网友 时间：2022-04-15 03:19

假设你的字段名和值是数据库里面唯一的。

由于你没给字段名和值我列举了一个吧
字段名和值分别为column_name=1

select table_schema,table_name from information_schema.columns where column_name = '1'

用这条语句就能查询出来,其中 table_schema 是所在库, table_name 是所在表

如果你这个字段名和值不是数据库里面唯一的，而且好几个表里面都有的，好像查不出来。

或者能查出来，可惜鄙人才疏学浅，并不掌握！

寥寥几语希望能给你启发和帮助！有错误的地方还请指正！追问感谢，这个方法我知道，可惜我不会写储存过程，每次都搞的死机，只能找大神写源码学习 O(∩_∩)O

热心网友 时间：2022-04-15 04:37

If Object_Id('TempDB..#Result') Is Not Null Drop Table #Result
Create Table #Result(Name Varchar(200))
Declare @TableName Varchar(200), @SQLAdd Varchar(500),
@Name Varchar(30) = '字段', --字段名
@Value Varchar(30) = '值'--对应值

--赋值
Declare cur Cursor Static READ_ONLY For
Select a.name
From syscolumns s JOIN sysobjects a ON s.id = a.id
Where s.name= @Name And a.xType = 'U'
Open cur
Fetch From cur Into @TableName
While @@FETCH_STATUS = 0
Begin
Set @SQLAdd = ' Insert Into #Result(Name) Select '''+ @TableName + ''' From '+ @TableName + ' Where ' + @Name + ' = ''' + @Value + ''''
Exec(@SQLAdd)
Fetch Next From cur Into @TableName
End
Close cur
DeAllocate cur
Select * From #Result追问

30多分钟就执行成功了，不过这个重复值太多了，是不是可以改进一下 O(∩_∩)O

追答重复值的话;j将Select * From #Result改成Select Distinct * From #Result就OK了

热心网友 时间：2022-04-15 06:12

这个只能做个函数或者存储过程，遍历系统表，来定位表格名称

热心网友 时间：2022-04-15 08:03

CREATE PROCEDURE [dbo].[SP_FindValueInDB](@value VARCHAR(1024)) ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;DECLARE @sql VARCHAR(1024) DECLARE @table VARCHAR(64) DECLARE @column VARCHAR(64) CREATE TABLE #t ( tablename VARCHAR(64), columnname VARCHAR(64) ) DECLARE TABLES CURSORFORSELECT o.name, c.name FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239) ORDER BY o.name, c.name OPEN TABLES FETCH NEXT FROM TABLES INTO @table, @columnWHILE @@FETCH_STATUS = 0BEGINSET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] 'SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') 'SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''SET @sql = @sql + @column + ''')'EXEC(@sql) FETCH NEXT FROM TABLES INTO @table, @columnENDCLOSE TABLES DEALLOCATE TABLES SELECT *FROM #t DROP TABLE #t End

热心网友 时间：2022-04-15 10:11

通过数据（张三）查找 表名和字段明
declare @cloumns varchar(40)

declare @tablename varchar(40)
declare @str varchar(40)
declare @counts int
declare @sql nvarchar(2000)
declare MyCursor Cursor For
Select a.name as Columns, b.name as TableName from syscolumns a,sysobjects b,systypes c
where a.id = b.id
and b.type = 'U'
and a.xtype=c.xtype
and c.name like '%char%'
set @str='张三'
Open MyCursor
Fetch next From MyCursor Into @cloumns,@tablename
While(@@Fetch_Status = 0)
Begin
set @sql='select @tmp_counts=count(*) from ' +@tablename+ ' where ' +@cloumns+' = ''' +@str+ ''''
execute sp_executesql @sql,N'@tmp_counts int out',@counts out
if @counts>0
begin
print '表名为:'+@tablename+',字段名为'+@cloumns
end
Fetch next From MyCursor Into @cloumns,@tablename
End
Close MyCursor
Deallocate MyCursor

热心网友 时间：2022-04-15 12:36

　　第一零九回：候芳魂五儿承错爱，还孽债迎女返真元

热心网友 时间：2022-04-15 15:17

Select table.name