新闻建站cms系统、政府cms系统定制开发

广州网站建设公司-阅速公司

asp.net新闻发布系统、报纸数字报系统方案
/
http://www.ysneo.com/
广州网站建设公司
您当前位置:首页>sqlserver数据库

sqlserver数据库

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

发布时间:2024/6/5 18:31:09  作者:Admin  阅读:7  

广告:阿里云采购优惠专区

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

广告:阿里云新人采购专场

相关文章
查询所有表
cms新闻系统购买咨询
扫描关注 广州阅速软件科技有限公司
扫描关注 广州阅速科技