Thread: [Squirrel-sql-develop] How do I get metadata about builtin functions from MS SqlServer?

Other databases have tables and functions for deriving the names, args 
and returntypes for builtin functions but I can't find this for 
sqlserver.  Does anyone on this list have any experience with that?

For example a query for postgres that we use is:

SELECT proname as name, 
pg_catalog.pg_get_function_arguments(pg_proc.oid) as input,
     format_type(pg_proc.prorettype, null) as returntype, prosrc as 
definition FROM pg_proc ;

Thanks in advance..

wcn

Sounds to me that you're looking for something like this:
https://www.mssqltips.com/sqlservertip/1669/generate-a-parameter-list-for-all-sql-server-stored-procedures-and-functions/
?

bye Jürgen

Am 01.11.18 um 21:01 schrieb Wendell Nichols:
> Other databases have tables and functions for deriving the names, args 
> and returntypes for builtin functions but I can't find this for 
> sqlserver.  Does anyone on this list have any experience with that?
>
> For example a query for postgres that we use is:
>
> SELECT proname as name, 
> pg_catalog.pg_get_function_arguments(pg_proc.oid) as input,
>     format_type(pg_proc.prorettype, null) as returntype, prosrc as 
> definition FROM pg_proc ;
>
> Thanks in advance..
>
> wcn
>
>
>
> _______________________________________________
> Squirrel-sql-develop mailing list
> Squ...@li...
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop

-- 
Jürgen Weltner
Uhlengrund 18 b
D-21244 Buchholz
04181 -137 118
0176 49 000 349

Thanks Jurgen.  This works for user defined functions and stored 
procedures but not builtin's like ADD.

I hate to do it but we're going to have to hard code the metadata for those.

wcn

On 11/2/18 12:12 AM, Jürgen Weltner wrote:
> Sounds to me that you're looking for something like this:
> https://www.mssqltips.com/sqlservertip/1669/generate-a-parameter-list-for-all-sql-server-stored-procedures-and-functions/ 
>
> ?
>
> bye Jürgen
>
> Am 01.11.18 um 21:01 schrieb Wendell Nichols:
>> Other databases have tables and functions for deriving the names, 
>> args and returntypes for builtin functions but I can't find this for 
>> sqlserver.  Does anyone on this list have any experience with that?
>>
>> For example a query for postgres that we use is:
>>
>> SELECT proname as name, 
>> pg_catalog.pg_get_function_arguments(pg_proc.oid) as input,
>>     format_type(pg_proc.prorettype, null) as returntype, prosrc as 
>> definition FROM pg_proc ;
>>
>> Thanks in advance..
>>
>> wcn
>>
>>
>>
>> _______________________________________________
>> Squirrel-sql-develop mailing list
>> Squ...@li...
>> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop
>

Hi Wendell,

still not sure what you exactly need.

ADD seems to be a DDL statement, while the values of your example 
postgres statement retrieves (as far as i understand) DML callables.

In SQLServer

select * from sys.all_objects

seems to retrieve all (not only user defined in the meaning of 
non-builtin users) functions and procedures.

But still not that set of built-in instructions like "Create", "Drop" or 
"group by" or "AVG", "DateAdd" and so on.

Is it that, what you're looking for? All SQLServer DDL /DML instructions?

bye Jürgen


Am 02.11.18 um 13:19 schrieb Wendell Nichols:
> Thanks Jurgen.  This works for user defined functions and stored 
> procedures but not builtin's like ADD.
>
> I hate to do it but we're going to have to hard code the metadata for 
> those.
>
> wcn
>
> On 11/2/18 12:12 AM, Jürgen Weltner wrote:
>> Sounds to me that you're looking for something like this:
>> https://www.mssqltips.com/sqlservertip/1669/generate-a-parameter-list-for-all-sql-server-stored-procedures-and-functions/ 
>>
>> ?
>>
>> bye Jürgen
>>
>> Am 01.11.18 um 21:01 schrieb Wendell Nichols:
>>> Other databases have tables and functions for deriving the names, 
>>> args and returntypes for builtin functions but I can't find this for 
>>> sqlserver.  Does anyone on this list have any experience with that?
>>>
>>> For example a query for postgres that we use is:
>>>
>>> SELECT proname as name, 
>>> pg_catalog.pg_get_function_arguments(pg_proc.oid) as input,
>>>     format_type(pg_proc.prorettype, null) as returntype, prosrc as 
>>> definition FROM pg_proc ;
>>>
>>> Thanks in advance..
>>>
>>> wcn
>>>
>>>
>>>
>>> _______________________________________________
>>> Squirrel-sql-develop mailing list
>>> Squ...@li...
>>> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop
>>
>
>
> _______________________________________________
> Squirrel-sql-develop mailing list
> Squ...@li...
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop

I guess I could have been more clear.  I don't work a whole lot with  
SQL databases, but the sorts of functions we need metadata for are
APPROX_COUNT_DISTINCT
AVG
CHECKSUM_AGG
COUNT
COUNT_BIG
GROUPING
GROUPING_ID
MAX
MIN
...

The queries I do on sys.all_objects return

sp_MSalreadyhavegeneration
sp_MSwritemergeperfcounter
sp_drop_trusted_assembly
TABLE_PRIVILEGES
sp_replsetsyncstatus
sp_replshowcmds
sp_publishdb
sp_addqueued_artinfo
sp_replcounters
sp_MSget_subscription_dts_info

...


I've been trying to figure out if the jdbc driver has code that would 
give me a hint but nothing so far.

Thanks for your time.

wcn

On 11/2/18 8:28 AM, Jürgen Weltner wrote:
> select * from sys.all_objects 

Ok. I never explored a corresponding function in SQLServer (or Oracle or 
DB2...).

I remember (but never get really in touch with it) corresponding (mostly 
java) stuff in frameworks where classes like Dialect (e.g: 
org.hibernate.dialect.Oracle10gDialect, 
https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/dialect/Oracle10gDialect.java) 
captures implementation specific instruction sets.

Here is a another example:
https://github.com/JetBrains/Exposed/blob/master/src/main/kotlin/org/jetbrains/exposed/sql/vendors/Keywords.kt

As you see, there has to be a kind of pattern implemented, which handles 
the different ANSI SQL approaches and vendor specific stuff.

bye jürgen

Am 02.11.18 um 18:44 schrieb Wendell Nichols:
> I guess I could have been more clear.  I don't work a whole lot with  
> SQL databases, but the sorts of functions we need metadata for are
> APPROX_COUNT_DISTINCT
> AVG
> CHECKSUM_AGG
> COUNT
> COUNT_BIG
> GROUPING
> GROUPING_ID
> MAX
> MIN
> ...
>
> The queries I do on sys.all_objects return
>
> sp_MSalreadyhavegeneration
> sp_MSwritemergeperfcounter
> sp_drop_trusted_assembly
> TABLE_PRIVILEGES
> sp_replsetsyncstatus
> sp_replshowcmds
> sp_publishdb
> sp_addqueued_artinfo
> sp_replcounters
> sp_MSget_subscription_dts_info
>
> ...
>
>
> I've been trying to figure out if the jdbc driver has code that would 
> give me a hint but nothing so far.
>
> Thanks for your time.
>
> wcn
>
> On 11/2/18 8:28 AM, Jürgen Weltner wrote:
>> select * from sys.all_objects