sqlserver.information_schema.ROUTINES.sql Maven / Gradle / Ivy

Go to download

Show more of this group Show more artifacts with this name
Show all versions of schemacrawler-sqlserver Show documentation

The newest version!

SELECT 
  R.ROUTINE_CATALOG,
  R.ROUTINE_SCHEMA,
  R.ROUTINE_NAME + ';' + CASE WHEN ALL_OBJECTS.IS_MS_SHIPPED = 1 THEN '0' ELSE COALESCE(CAST(NUM.PROCEDURE_NUMBER AS CHAR), '1') END
    AS ROUTINE_NAME, -- Hack for Microsoft SQL Server JDBC driver
  NULL AS SPECIFIC_NAME,
  R.ROUTINE_TYPE,
  R.DATA_TYPE,
  R.CHARACTER_MAXIMUM_LENGTH,
  R.CHARACTER_OCTET_LENGTH,
  R.NUMERIC_PRECISION,
  R.NUMERIC_PRECISION_RADIX,
  R.NUMERIC_SCALE,
  R.DATETIME_PRECISION,
  R.ROUTINE_BODY,
  OBJECT_DEFINITION(OBJECT_ID(R.ROUTINE_CATALOG + '.' + R.ROUTINE_SCHEMA + '.' + R.ROUTINE_NAME))
    AS ROUTINE_DEFINITION,
  R.IS_DETERMINISTIC,
  R.SQL_DATA_ACCESS,
  R.IS_NULL_CALL,
  R.MAX_DYNAMIC_RESULT_SETS,
  R.CREATED,
  R.LAST_ALTERED,
  ALL_OBJECTS.OBJECT_ID
FROM 
  INFORMATION_SCHEMA.ROUTINES R
  INNER JOIN SYS.ALL_OBJECTS
    ON OBJECT_ID(R.ROUTINE_CATALOG + '.' + R.ROUTINE_SCHEMA + '.' + R.ROUTINE_NAME) = ALL_OBJECTS.OBJECT_ID
  LEFT OUTER JOIN SYS.NUMBERED_PROCEDURES NUM
    ON ALL_OBJECTS.OBJECT_ID = NUM.OBJECT_ID