DECLARE TableCursor CURSOR FOR SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName, ty.name AS DataType FROM sys.tables t INNER JOIN sys.columns c ON t.object_id = c.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id WHERE ty.name IN ('nvarchar','varchar','char','nchar','text','ntext') -- and t.name = 'T_META_OBJECTTYPECACHE'
OPEN TableCursor FETCH NEXT FROM TableCursor INTO @Schema, @TableName, @ColumnName, @DataType
WHILE @@FETCH_STATUS = 0 BEGIN -- 对text/ntext类型字段特殊处理 IF @DataType IN ('text', 'ntext') BEGIN SET @SQL = 'INSERT INTO Tmp_FindTxt (SchemaName, TableName, ColumnName, ColumnValue, MatchCount) SELECT ''' + REPLACE(@Schema, '''', '''''') + ''', ''' + REPLACE(@TableName, '''', '''''') + ''', ''' + REPLACE(@ColumnName, '''', '''''') + ''', CAST(SUBSTRING(CAST(' + QUOTENAME(@ColumnName) + ' AS NVARCHAR(MAX)), 1, 100) AS NVARCHAR(100)), COUNT(*) AS MatchCount FROM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + ' WHERE CAST(' + QUOTENAME(@ColumnName) + ' AS NVARCHAR(MAX)) LIKE ''%' + REPLACE(@SearchStr, '''', '''''') + '%'' GROUP BY CAST(' + QUOTENAME(@ColumnName) + ' AS NVARCHAR(MAX))' END ELSE BEGIN SET @SQL = 'INSERT INTO Tmp_FindTxt (SchemaName, TableName, ColumnName, ColumnValue, MatchCount) SELECT ''' + REPLACE(@Schema, '''', '''''') + ''', ''' + REPLACE(@TableName, '''', '''''') + ''', ''' + REPLACE(@ColumnName, '''', '''''') + ''', CAST(' + QUOTENAME(@ColumnName) + ' AS NVARCHAR(MAX)), COUNT(*) AS MatchCount FROM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@ColumnName) + ' LIKE ''%' + REPLACE(@SearchStr, '''', '''''') + '%'' GROUP BY ' + QUOTENAME(@ColumnName) END
BEGIN TRY EXEC sp_executesql @SQL END TRY BEGIN CATCH PRINT 'Error searching [' + @Schema + '].[' + @TableName + '].[' + @ColumnName + ']: ' + ERROR_MESSAGE() END CATCH
FETCH NEXT FROM TableCursor INTO @Schema, @TableName, @ColumnName, @DataType END
CLOSE TableCursor DEALLOCATE TableCursor
-- 显示结果
SELECT
'select '+ ColumnName +' ,* from '+ TableName +' where '+ ColumnName +' like ''%' + REPLACE(@SearchStr, '''', '''''') + '%''' AS '查询',
SchemaName AS '架构名', TableName AS '表名', ColumnName AS '字段名', ColumnValue AS '包含的字段值', MatchCount AS '匹配行数' FROM Tmp_FindTxt ORDER BY SchemaName, TableName, ColumnName
结果：
查询 架构名 表名 字段名 包含的字段值 匹配行数 select FID ,* from T_META_OBJECTTYPECACHE where FID like '%99291303-1d0d-42d2-9c5e-5ce8153172d2%' dbo T_META_OBJECTTYPECACHE FID 99291303-1d0d-42d2-9c5e-5ce8153172d2 1 郭少锋编辑 2025-10-16 11:05:11创建 2025-10-16 11:04:35SQL云星空 : 读取 系统设置云星空 : 读取 系统设置 var aUserId = aContext.UserId.ToString(); // 当前用户 id var aFOrgId = aContext.CurrentOrganizationInfo.ID; // 当前组织 id
#region| 取得：系统设置 |
var aSql = $@"/*dialect*/ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -- 读取：系统设置
select * from ( -------------------------------------------------------------------------------------------- select FOrgId
where FParameterObjId = 'aGsf_SysSet' -------------------------------------------------------------------------------------------- ) a where FUserId = {aUserId} and FOrgId in ( 0,{aFOrgId} )
order by FOrgId desc
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- "; DynamicObjectCollection aTable1 = DBServiceHelper.ExecuteDynamicObject(aContext, aSql); #endregion 郭少锋创建 2025-10-10 10:08:45云星空SQL : 读取 xml 字段SQL : 读取 xml 字段 ------------------------------------------------------------------------------------------------------------------------ -- 读取 xml 成 table
select * from ( -------------------------------------------------------------------------------------------- select
a1.value('(Seq)[1]', 'int') AS Seq, a1.value('(FUserId/User/Id)[1]', 'int') AS UserId, a1.value('(FUserId/User/Name)[1]', 'nvarchar(50)') AS UserName, a1.value('(FType)[1]', 'nvarchar(50)') AS FType, a1.value('(FValue)[1]', 'int') AS FValue
where FParameterObjId = 'aGsf_SysSet' -------------------------------------------------------------------------------------------- ) a where UserName = '郭少锋'
/* select FParameters from t_Bas_SysParameter where FParameterObjId = 'aGsf_SysSet'
------------------------------------------------------------------------------------------------------------------------ -- 取得 xml
from TMP2983AE495E5511F0ACB40050563 a join TMP2983AE495E5511F0ACB40050563 b on ( b.FStockName = a.FStockName ) and ( b.FMtrlNo = a.FMtrlNo ) and ( b.FMtrlName = a.FMtrlName ) and ( b.FUnitStockName = a.FUnitStockName )
and ( b.FIdentityId <= a.FIdentityId )
group by a.FStockName,a.FMtrlNo,a.FMtrlName,a.FUnitStockName ,a.FIdentityId ,a.FQtyStock4
order by a.FStockName,a.FMtrlNo,a.FMtrlName,a.FUnitStockName ,a.FIdentityId ;
SELECT a.group_column, a.date_column, a.value_column, SUM(b.value_column) AS group_running_total FROM your_table a JOIN your_table b ON b.group_column = a.group_column AND b.date_column < a.date_column GROUP BY a.group_column, a.date_column, a.value_column ORDER BY a.group_column, a.date_column;郭少锋编辑 2025-07-11 21:43:13创建 2025-07-11 21:07:36SQL