Gerador para Stored e Procedure

[bjverde]

Criar o gerador de tela e api partindo de uma Stored and Procedure assim como feito com tabelas quando o banco escolhido for MS Sql Server

[bjverde]

Select do Ms Sql Server para listar todas as SP de um banco

 SELECT Schema_name(schema_id)   AS [Schema],
       SO.NAME                   AS [ObjectName],
       SO.type_desc              AS [ObjectType (UDF/SP)],
       P.parameter_id            AS [ParameterID],
       P.NAME                    AS [ParameterName],
       Type_name(P.user_type_id) AS [ParameterDataType],
       P.max_length              AS [ParameterMaxBytes],
       P.is_output               AS [IsOutPutParameter]
FROM   sys.objects AS SO
       INNER JOIN sys.parameters AS P
               ON SO.object_id = P.object_id
WHERE  SO.object_id IN (SELECT object_id
                        FROM   sys.objects
                        WHERE  type IN ( 'P', 'FN' ))
       -- AND SO.NAME = 'P_ManifestacaoResposta'
ORDER  BY [schema],
          SO.NAME,
          P.parameter_id  

[bjverde]

https://docs.microsoft.com/pt-br/sql/relational-databases/system-stored-procedures/sp-helptext-transact-sql?view=sql-server-2017

[bjverde]

A primeira parte é melhorar o que aparece em https://github.com/bjverde/formDin/blob/master/base/classes/webform/TDAO.class.php#L1467

A segunda parte é melhorar o que aparece em https://github.com/bjverde/formDin/blob/master/base/classes/webform/TDAO.class.php#L1560

A terceira parte é criar os gerador de DAO, VO, Controller, Form e API

[bjverde]

Para resolver a primeira parte o SQL fica

select 
TABLE_SCHEMA
,TABLE_NAME
,COLUMN_QTD
,TABLE_TYPE
from (
SELECT qtd.TABLE_SCHEMA
        ,qtd.TABLE_NAME
        ,qtd.COLUMN_QTD
        ,case ty.TABLE_TYPE WHEN 'BASE TABLE' THEN 'TABLE' ELSE ty.TABLE_TYPE end as TABLE_TYPE
FROM
    (SELECT TABLE_SCHEMA
            ,TABLE_NAME
            ,COUNT(TABLE_NAME) COLUMN_QTD
    FROM INFORMATION_SCHEMA.COLUMNS c
    where c.TABLE_SCHEMA <> 'METADADOS'
    group by TABLE_SCHEMA, TABLE_NAME
    ) as qtd
    ,(SELECT TABLE_SCHEMA
            , TABLE_NAME
            , TABLE_TYPE
    FROM INFORMATION_SCHEMA.TABLES i
    where I.TABLE_SCHEMA <> 'METADADOS'
    ) as ty
where qtd.TABLE_SCHEMA = ty.TABLE_SCHEMA
and qtd.TABLE_NAME = ty.TABLE_NAME

UNION

 SELECT Schema_name(schema_id)   AS TABLE_SCHEMA,
       SO.NAME                   AS TABLE_NAME,       
       count(*)                  AS COLUMN_QTD,
       CASE SO.type_desc 
       WHEN  'SQL_STORED_PROCEDURE' THEN 'PROCEDURE'
       ELSE 'FUNCTION' 
       END AS TABLE_TYPE       
FROM   sys.objects AS SO
       INNER JOIN sys.parameters AS P
               ON SO.object_id = P.object_id
WHERE  SO.object_id IN (SELECT object_id
                        FROM   sys.objects
                        WHERE  type IN ( 'P', 'FN' ))
group by schema_id, SO.NAME, SO.type_desc
) as res
order by res.TABLE_SCHEMA, res.TABLE_NAME

[bjverde]

Na primeira versão NÃO será trato : 'FN' - SQL_SCALAR_FUNCTION 'TF' - SQL_TABLE_VALUED_FUNCTION

[bjverde]

Para resolver a segunda parte o SQL fica

SELECT P.NAME                    AS COLUMN_NAME
       ,null                      AS REQUIRED
       ,Type_name(P.user_type_id) AS DATA_TYPE
       ,P.max_length              AS CHAR_MAX
       ,null                      AS NUM_LENGTH
       ,null                      AS NUM_SCALE
       ,null                      AS COLUMN_COMMENT
       ,null                      AS COLUMN_COMMENT
       ,null                      AS KEY_TYPE
       ,null                      AS REFERENCED_TABLE_NAME
       ,null                      AS REFERENCED_COLUMN_NAME
       ,Schema_name(schema_id)    AS TABLE_SCHEMA
       ,SO.NAME                   AS table_name
FROM   sys.objects AS SO
       INNER JOIN sys.parameters AS P
               ON SO.object_id = P.object_id
WHERE  SO.object_id IN (SELECT object_id
                        FROM   sys.objects
                        WHERE  type IN ( 'P'))