public void GetStoredProcedureMetaDataDetails(StoredProcedureMetaData storedProcedureMetadata, string connStr)
{
int indexCount = 0;
ObservableCollection <ParameterMetaData> parameters = new ObservableCollection <ParameterMetaData>();
string sql = string.Format(@"select
PARAMETER_NAME
, value as Description
, CASE WHEN VALUE IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END AS HasDescription
from INFORMATION_SCHEMA.PARAMETERS
outer apply
(
select value from fn_listextendedProperty('{2}',
'SCHEMA', SPECIFIC_SCHEMA
, 'PROCEDURE', SPECIFIC_NAME
, 'PARAMETER', PARAMETER_NAME)
) descr
where SPECIFIC_NAME = '{0}' and SPECIFIC_SCHEMA = '{1}' ", storedProcedureMetadata.Level1Name, storedProcedureMetadata.Schema, ApplicationSettings.Default.ExtendedPropKey);
using (DbCommand cmd = CommandFactory.Create(sql, connStr))
{
cmd.Connection.Open();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
parameters.Add(new ParameterMetaData
(
storedProcedureMetadata.Level1Name,
reader.GetString(reader.GetOrdinal("PARAMETER_NAME")),
storedProcedureMetadata.Schema,
reader.GetStringOrEmpty(reader.GetOrdinal("Description")),
!reader.GetBoolean(reader.GetOrdinal("HasDescription")),
indexCount++
));
}
reader.Close();
}
storedProcedureMetadata.Parameters = parameters;
}
/// <summary>
///
/// </summary>
/// <param name="connStr"></param>
/// <returns></returns>
private string GenerateWikiMarkupForStoredProcedures(string connStr, StoredProcedureMetaData spMetadata)
{
string result = string.Empty;
StringBuilder sb = new StringBuilder();
string sql = @"
SELECT
WikiMarkup
FROM
sys.objects so
outer APPLY
(
SELECT (
'h2. ' + SCHEMA_NAME(so.schema_id) + '.' + so.Name + '
\\' +
ISNULL( (SELECT CAST(VALUE AS NVARCHAR(MAX))
FROM
fn_listextendedProperty(
@ExtendedPropertyName
, 'SCHEMA', SCHEMA_NAME(so.schema_id)
,'PROCEDURE', so.Name
, NULL, NULL)), '{color:#ff0000}{*}DESCRIPTION MISSING{*}{color}')
) AS WikiMarkup
UNION ALL
SELECT CASE
WHEN EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.parameters
where SPECIFIC_NAME = so.name and SPECIFIC_SCHEMA = schema_name(so.schema_id)
)
THEN
'||Parameter Name|Datatype|Default value?|Is Output column|Description|'
ELSE
'No parameters'
END
UNION ALL
SELECT
'|' + p.name + '|' +
--(,Scale)
CASE
WHEN system_type_id IN (231,167,175,239) THEN
CAST(TYPE_NAME(p.system_type_id) AS NVARCHAR(100)) + '(' + CAST(max_length AS NVARCHAR(50)) + ')'
WHEN system_type_id = 106 THEN
CAST(TYPE_NAME(p.system_type_id) AS NVARCHAR(100)) + '(' + CAST(PRECISION AS NVARCHAR(50)) + ', ' + CAST(Scale AS NVARCHAR(50)) + ')'
ELSE
TYPE_NAME(p.system_type_id)
END
+ '|' +
CAST(ISNULL(p.default_value, 'N/a') AS NVARCHAR(100)) + '|' +
CASE p.is_output WHEN 1 THEN 'Yes' ELSE 'No' END + '|' +
ISNULL(CAST(VALUE AS NVARCHAR(MAX)), '{color:#ff0000}{*}DESCRIPTION MISSING{*}{color}') + '|'
FROM
sys.parameters p
outer APPLY
fn_listextendedProperty(
@ExtendedPropertyName
,'SCHEMA', SCHEMA_NAME(so.schema_id)
,'PROCEDURE', so.Name
,'PARAMETER', p.name)
WHERE
p.object_id = so.object_id
UNION ALL
SELECT '
\\\
'
) spInfo
WHERE
so.type IN ('p', 'fn', 'if', 'tf')
and is_ms_shipped = 0
and
(@SPName IS NULL or (name = @SPName and schema_id(@SPSchema) = schema_id))
ORDER BY
so.name";// ApplicationSettings.Default.ExtendedPropKey);
using (DbCommand cmd = CommandFactory.Create(sql, connStr))
{
// If we are requesting wikimarkup for a specific stored procedure then this sql code need to be included in the search clause.
if (spMetadata != null)
{
cmd.Parameters.Add(CommandFactory.CreateParameter("SPName", spMetadata.Level1Name));
cmd.Parameters.Add(CommandFactory.CreateParameter("SPSchema", spMetadata.Schema));
cmd.Parameters.Add(CommandFactory.CreateParameter("ExtendedPropertyName", ApplicationSettings.Default.ExtendedPropKey));
}
else
{
cmd.Parameters.Add(CommandFactory.CreateParameter("SPName", DBNull.Value));
cmd.Parameters.Add(CommandFactory.CreateParameter("SPSchema", DBNull.Value));
cmd.Parameters.Add(CommandFactory.CreateParameter("ExtendedPropertyName", ApplicationSettings.Default.ExtendedPropKey));
}
cmd.Connection.Open();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
sb.AppendLine(reader[0].ToString());
}
}
return(sb.ToString());
}
