Functions Overview

I use this query in MS Excel to view the parameters in each of the user-defined functions in a specific database. It also provides me with an overview into the design of return tables for table functions.

select  sysobjects.[name] as FuncName
        ,ParamName
                = case
                        when    syscolumns.[name] = ''
                                then    'RETURNS'
                        else    syscolumns.[name]
                        end
        ,systypes.[name] as Datatype
        ,syscolumns.length as Length
        ,'' as ReturnTable
        from    sysobjects
                left join       syscolumns
                on      syscolumns.[id] = sysobjects.[id]
                inner join      systypes
                on      systypes.xusertype = syscolumns.xusertype
        where   (sysobjects.xtype in ('FN', 'IF', 'TF'))
                and (left(sysobjects.[name], 3) != 'dt_')
                and (left(syscolumns.[name], 1) = '@'
                        or syscolumns.[name] is null
                        or syscolumns.[name] = '')
union
select  sysobjects.[name] as FuncName
        ,'RETURNS TABLE' as ParamName
        ,'' as Datatype
        ,-1 as Length
        ,ReturnTable
                = (
                select  substring(substring(syscomments.[text], 
                        patindex('%returns%', syscomments.[text]), 
                        patindex('%as' + char(13) + '%', 
                        syscomments.[text]) - patindex('%returns%', 
                        syscomments.[text])), patindex('%table%', 
                        substring(syscomments.[text], 
                        patindex('%returns%', syscomments.[text]), 
                        patindex('%as' + char(13) + '%', 
                        syscomments.[text]) - patindex('%returns%', 
                        syscomments.[text]))), 
                        len(substring(syscomments.[text], 
                        patindex('%returns%', syscomments.[text]), 
                        patindex('%as' + char(13) + '%', 
                        syscomments.[text]) - patindex('%returns%', 
                        syscomments.[text]))))
                        from    syscomments
                        where   (syscomments.[id] = sysobjects.[id])
                )
        from    sysobjects
                left join       syscolumns
                                on      syscolumns.[id] is null
        where   (sysobjects.xtype in ('TF'))
                and (left(sysobjects.[name], 3) != 'dt_')
                and (left(syscolumns.[name], 1) = '@'
                        or syscolumns.[name] is null
                        or syscolumns.[name] = '')
        order by        FuncName

The result-set is a flat table. ML

Procedures Overview

I use this query in MS Excel to view the parameters in each of the procedures in a specific database.

select  sysobjects.[name] as ProcName
        ,syscolumns.[name] as ParamName
        ,systypes.[name] as Datatype
        ,syscolumns.length as Length
        ,Special
                = case  syscolumns.isoutparam
                        when    1
                                then    'output'
                        else    ''
                        end
        from    sysobjects
                left join       syscolumns
                on      syscolumns.[id] = sysobjects.[id]
                inner join      systypes
                on      systypes.xusertype = syscolumns.xusertype
        where   (sysobjects.xtype = 'P')
        order by        ProcName
                        ,syscolumns.colorder

The result-set is a flat table. ML

The First One

I started this blog to store and to share little bits and pieces of T-SQL code. They are but snippets and scripts which have brought efficiency into my work with SQL.

I sincerely hope they do the same for you.

ML