【BOS平台】--通过存储过程得到所有LK表原创
5人赞赏了该文章
28次浏览
未经作者许可,禁止转载编辑于2024年08月20日 09:12:58
1、结果运行如下:
2、sqlserver存储过程
if(OBJECT_ID('Proc_GetLinkTbName') is not null) begin drop proc Proc_GetLinkTbName end go --创建存储过程 Create Procedure Proc_GetLinkTbName as begin --临时表 Create table #tempTb ( FBillName nvarchar(100) , FFormId nvarchar(100), FLinkTbName nvarchar(100) ) Declare @formId nvarchar(100); Declare myCursor Cursor for select FId from t_meta_objectType where FModelTypeId in(100,400); open myCursor Fetch next from myCursor into @formId; while @@Fetch_status = 0 begin insert into #tempTb(FBillName,FFormId,FLinkTbName) select tt.FName, tt.FId, Item.value('text()[1]','nvarchar(100)') FLinkTbName from (select CAST(t0.FKernelXML as XML) fxml ,t0.FID,t1.FNAME from T_META_OBJECTTYPE t0 left join T_META_OBJECTTYPE_L t1 on t1.FId = t0.FId and t1.FLocaleId=2052 where t0.FID=@formId) tt cross apply tt.fxml.nodes('//LinkEntitys/LinkEntity/TableName') as tb(Item); Fetch next from myCursor into @formId ; end select *,Row_number() over(order by FFormId) as FIndex from #tempTb close myCursor deallocate myCursor end
推荐阅读