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

广告：