<?
query --
SELECT * FROM #tbl_result r
--?>
this is the query
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 int,@P3 nvarchar(4000))SET NOCOUNT ON
IF Object_id('tempdb..#tbl_wf') IS NOT NULL DROP TABLE #tbl_wf; SET NOCOUNT ON IF
Object_id('tempdb..#tbl_result') IS NOT NULL DROP TABLE #tbl_result; SELECT
AWS.object_id ,CASE WHEN AWN.node_type = 'S' THEN ( SELECT status_title FROM
ads_workflow_stage WHERE wfs_id = AWN.node_id ) ELSE ( SELECT status_title FROM
ads_workflow_trigger_action WHERE wfta_id = AWN.node_id ) END AS STATUS_TITLE INTO
#tbl_wf FROM ads_workflow_status AWS JOIN ads_workflow_node AWN ON AWN.wfn_id =
AWS.wfn_id JOIN ads_workflow AW ON AW.wf_id = AWN.wf_id JOIN
ads_workflow_trigger_action AWTA ON AWTA.wfta_id = AWN.node_id WHERE
AWS.object_type_id = @P0 ; DELETE FROM #tbl_wf WHERE object_id not in (SELECT
STCM_ID FROM VALID_SYS_TBL_COL_METADATA); INSERT INTO #tbl_wf SELECT
objectid ,'Publish' FROM rm_history WHERE objecttypeid = @P1 AND historytypeid =
@P2 AND historyid IN ( SELECT Max(historyid) FROM rm_history WHERE objecttypeid =
@P3 GROUP BY objectid ,objecttypeid ) AND NOT EXISTS ( SELECT
object_id ,object_type_id FROM ads_workflow_status WHERE object_id = objectid AND
object_type_id = objecttypeid ) AND objectid IN ( SELECT STCM_ID FROM
SYSTEM_TABLE_COLUMNS_METADATA(nolock) WHERE publish = '1' ); SELECT DISTINCT
tblcols.stm_id ,tblcols.ref_id ,asce.SYSTEM_ENVIRONMENT_NAME AS
SYSTEM_ENV_NAME ,asce.system_id ,tblcols.stcm_id ,tblcols.sce_id ,asce.database_typ
e ,asce.system_name ,Isnull(vstm.expanded_logical_name, '') AS
expanded_table_logical_name ,Isnull(vstm.logical_table_name, '') AS
table_logical_name ,Isnull(tblcols.sdi_description, '') AS
sdi_description ,tblcols.sdi_flag ,[Link] ,tblcols.created_by ,tblcols.last_m
odified_by ,tblcols.last_modified_date_time ,tblcols.created_datetime ,Isnull(tblco
ls.logical_column_name, '') AS logical_column_name ,Isnull(tblcols.column_comments,
'') AS column_comments ,Isnull(tblcols.col_def, '') AS
col_def ,Isnull(tblcols.expanded_logical_name, '') AS
expanded_logical_name ,Isnull(tblcols.user_defination1, '') AS
user_defination1 ,Isnull(tblcols.user_defination2, '') AS
user_defination2 ,Isnull(tblcols.user_defination3, '') AS
user_defination3 ,Isnull(tblcols.user_defination4, '') AS
user_defination4 ,Isnull(tblcols.user_defination5, '') AS
user_defination5 ,Isnull(tblcols.user_defination6, '') AS
user_defination6 ,Isnull(tblcols.user_defination7, '') AS
user_defination7 ,Isnull(tblcols.user_defination8, '') AS
user_defination8 ,Isnull(tblcols.user_defination9, '') AS
user_defination9 ,Isnull(tblcols.user_defination10, '') AS
user_defination10 ,Isnull(tblcols.data_steward_name, '') AS
data_steward_name ,tblcols.data_profile_score ,Isnull(tblcols.dqtool_score, '') AS
dqtool_score ,Isnull(asce.dqtool_flag, '') AS
dqtool_flag ,Isnull(tblcols.impact_score, '') AS impact_score ,0 AS
isRootTable ,Cast(vSTM.table_name + ':' + Cast(vSTM.stm_id AS VARCHAR) AS
NVARCHAR(max)) AS nodeId ,Cast(tblcols.declaration_path AS NVARCHAR(max)) as
xpath ,tblcols.column_name AS columnname ,Isnull(tblcols.col_datatype, '') AS
columndatatype ,Isnull(tblcols.col_length, '') AS
col_length ,Isnull(tblcols.col_precision, '') AS
PRECISION ,Isnull(tblcols.col_scale, '') AS
scale ,Isnull(tblcols.col_nullable_flag, '') AS
nullableflag ,Isnull(tblcols.primary_key_flag, '') AS
primarykeyflag ,tblcols.table_name AS tablename ,cast(null as nvarchar(50)) as
status_title INTO #tbl_result FROM VALID_SYS_TBL_COL_METADATA(nolock) AS tblcols
JOIN ACTIVE_SYS_CON_ENVIRONMENTS asce ON asce.sce_id = tblcols.SCE_ID JOIN
VALID_SYSTEM_TABLE_METADATA vstm ON vstm.STM_ID = tblcols.STM_ID; UPDATE tr SET
tr.status_title = wfs.STATUS_TITLE FROM #tbl_result tr JOIN #tbl_wf wfs ON
wfs.object_id = tr.stcm_id; SELECT * FROM #tbl_result r