sqlserver根据查询数据库中指定的数值在哪个表全库全表查询内容

广告：

GO

/****** Object: StoredProcedure [dbo].[FindValueInAllTables] Script Date: 2024-06-05 16:35:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[FindValueInAllTables]
 @SearchValue DECIMAL
AS
BEGIN
 SET NOCOUNT ON;
 
 DECLARE @TableName NVARCHAR(256);
 DECLARE @ColumnName NVARCHAR(256);
 DECLARE @SearchStr NVARCHAR(256);
 DECLARE @SQL NVARCHAR(MAX);
 
 DECLARE Cur CURSOR FOR 
 SELECT TABLE_NAME, COLUMN_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE DATA_TYPE IN ('decimal', 'numeric', 'float', 'real', 'money', 'smallmoney', 'int', 'bigint', 'tinyint')
 ORDER BY TABLE_NAME, ORDINAL_POSITION;
 
 OPEN Cur;
 
 FETCH NEXT FROM Cur INTO @TableName, @ColumnName;
 
 WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @SearchStr = 'SELECT * FROM [' + @TableName + '] WHERE [' + @ColumnName + '] = ' + CAST(@SearchValue AS NVARCHAR);
 EXEC sp_executesql @SearchStr;
 
 FETCH NEXT FROM Cur INTO @TableName, @ColumnName;
 END
 
 CLOSE Cur;
 DEALLOCATE Cur;
END

GO

然后执行：EXEC FindValueInAllTables @SearchValue = 7005;

第二步根据字段名称查询表名,在sqlserver中如何根据字段名查找字段所在的表:记下相应的值如： tid = 304387

select a.name TableName, b.name FieldName
from sysobjects a,syscolumns b 
where a.id=b.id and b.name='FieldName' and a.type='U'

找到表名：
select * from tablename where tid = 304387
UPDATE tablename SET DATETIME='2024-06-05 00:25:53' where tid = 304387

查找中文内容：

USE [dpyd2024]
GO
/****** Object: StoredProcedure [dbo].[FindValueInAllTables]  Script Date: 02/11/2025 16:54:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[FindValueInAllTables]
 @SearchValue nvarchar
AS
BEGIN
 SET NOCOUNT ON;
 
 DECLARE @TableName NVARCHAR(256);
 DECLARE @ColumnName NVARCHAR(256);
 DECLARE @SearchStr NVARCHAR(256);
 DECLARE @SQL NVARCHAR(MAX);
 
 DECLARE Cur CURSOR FOR 
 SELECT TABLE_NAME, COLUMN_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE DATA_TYPE IN ('nvarchar', 'varchar')
 ORDER BY TABLE_NAME, ORDINAL_POSITION;
 
 OPEN Cur;
 
 FETCH NEXT FROM Cur INTO @TableName, @ColumnName;
 
  -- 声明一个变量来存储计数结果
  DECLARE @TotalCount INT;
   DECLARE @SearchStr2 NVARCHAR(256);
 DECLARE @show_num varchar(9);
 WHILE @@FETCH_STATUS = 0
 BEGIN
 
 SET @SearchStr = 'SELECT * FROM [' + @TableName + '] WHERE [' + @ColumnName + '] like '''+@SearchValue+'%'' ';
 SET @SearchStr2 = 'SELECT @a = count(*) FROM [' + @TableName + '] WHERE [' + @ColumnName + '] like '''+@SearchValue+'%'' ';
 
 exec sp_executesql @SearchStr2,N'@a int output',@show_num output ;
 set @TotalCount=cast(@show_num as int);
 
 ---if
 if @TotalCount > 0
 begin 
 select @TableName ;
 select @SearchStr ;
  EXEC sp_executesql @SearchStr;
 end 
 ---if 
 FETCH NEXT FROM Cur INTO @TableName, @ColumnName;
 END
 
 CLOSE Cur;
 DEALLOCATE Cur;
END

EXEC FindValueInAllTables @SearchValue = '珊珊';

广告：