-
Notifications
You must be signed in to change notification settings - Fork 9
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Gerador para Stored e Procedure #89
Comments
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 |
A primeira parte é melhorar o que aparece em A segunda parte é melhorar o que aparece em A terceira parte é criar os gerador de DAO, VO, Controller, Form e API |
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 |
Na primeira versão NÃO será trato : |
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')) |
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
The text was updated successfully, but these errors were encountered: