Thursday, February 03, 2005

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

No comments: