SQL Server – Linked Server – Stored Procedures – Parameter List

Background

Needing to quickly issue Stored Procedure calls against a Linked Server.

To do so need to know which parameters the SP expects to receive.

Code

Objective

Unfortunately it is difficult to seamlessly write a generic code that will apply against any Linked Server and database combination.

Naming Convention

Here is one that targets :-

  1. Linked Server Name :- LS
  2. Database :- dbContext

Please replace both with your Linked Server alias and contextual database.

Choices

  1. System Stored Procedure
    • sp_help
  2. System Catalog Views
  3. Information Schema Views

SQL

SQL Server System Views

Code

Syntax


exec [LS].[dbContext].dbo.sp_help 'object-name'

Sample

exec [LS].[dbContext].dbo.sp_help '[usp].[listNames]'

Output

sp_help.20190223.0815PM.PNG

SQL Server System Views

Code


; with cteDataType

(
      object_id
    , parameter_id
    , [datatype]
    , [maxLength]
    , [maxLenghtInferred]
)
as
(

    select
              tblP.object_id
            , tblP.parameter_id
            , tblST.[name]
            , tblP.max_length
            , [lengthInferred]
                =   case tblST.name

                        when 'varchar' then
                                '('
                                + cast
                                    (
                                        tblP.max_length as varchar(10)
                                    )
                                + ')'

                        when 'nvarchar' then
                                '('
                                + cast(tblP.max_length as varchar(10))
                                + ')'

                        else ''
                     end

    from  [LS].[dbContext].sys.parameters tblP 

    JOIN [LS].[dbContext].sys.types tblST
            ON  tblP.system_type_id = tblST.system_type_id
            AND tblP.user_type_id = tblST.user_type_id

)
SELECT

      [object]
        = quoteName(tblSS.[name])
            + '.'
            + quoteName(sp.[name])

    , [parameterId]
        = p.parameter_id

    , [parameter]
        = p.[name]

    , [datatype]
        = cteDT.[datatype]

    , [maxLenghth]
        = p.[max_length]

    , p.is_nullable

    , p.has_default_value

    , p.default_value

    , p.is_output

    , [parmDeclaration]
        =
              'declare '
            + p.[name]
            + ' '
            + cteDT.datatype
            + cteDT.[maxLenghtInferred]

            COLLATE DATABASE_DEFAULT

    , [parmSend]
        =
            case p.[parameter_id]
                when 1 then ' '
                else ', '
            end
            + p.[name]
            + ' = '
            + p.[name]

            COLLATE DATABASE_DEFAULT

FROM [LS].[dbContext].sys.procedures sp

JOIN [LS].[dbContext].sys.schemas tblSS
    ON sp.schema_id = tblSS.[schema_id]

JOIN [LS].[dbContext].sys.parameters p
    ON sp.object_id = p.object_id

INNER JOIN cteDataType cteDT
        on  p.object_id = cteDT.object_id
        and p.parameter_id = cteDT.parameter_id

order by
          tblSS.[name]
        , sp.[name]
        , p.parameter_id

Output

paramters.20190223.1143AM.PNG

INFORMATION_SCHEMA

SQL


; with cteDataType

(
      [catalog]
    , [schema]
    , [object]
    , parameter_id
    , [datatype]
    , [maxLength]
    , [maxLenghtInferred]
)
as
(

    select
          tblP.SPECIFIC_CATALOG
        , tblP.SPECIFIC_SCHEMA
        , tblP.SPECIFIC_NAME
        , tblP.ORDINAL_POSITION
        , tblP.[DATA_TYPE]
        , tblP.CHARACTER_MAXIMUM_LENGTH
        , [lengthInferred]
            =   case tblP.[DATA_TYPE]

                    when 'varchar' then
                            '('
                            + cast
                                (

                                    case
                                        when [tblP].[CHARACTER_MAXIMUM_LENGTH] =
                                            -1 then 'MAX'
                                        else [tblP].[CHARACTER_MAXIMUM_LENGTH]
                                    end
                                        as varchar(10)

                                )
                            + ')'

                    when 'nvarchar' then
                            '('
                            +   cast
                                (
                                    case
                                        when tblP.[CHARACTER_MAXIMUM_LENGTH] =
                                            -1 then 'MAX'
                                        else tblP.[CHARACTER_MAXIMUM_LENGTH]
                                    end
                                        as varchar(10)
                                )

                            + ')'

                    else ''
                 end

    from  [LS].[dbContext].INFORMATION_SCHEMA.PARAMETERS  tblP 

)

SELECT

      [object]
        = quoteName([sp].[SPECIFIC_SCHEMA])
            + '.'
            + quoteName(sp.[SPECIFIC_NAME])

    , [parameterId]
        = p.ORDINAL_POSITION

    , [parameter]
        = p.[PARAMETER_NAME]

    , [datatype]
        = cteDT.[datatype]

    , [maxLenghth]
        = p.[CHARACTER_MAXIMUM_LENGTH]

    , p.PARAMETER_MODE

    , [is_output]
        = case p.PARAMETER_MODE
                when 'IN' then 0
                when 'OUT' then 1
                when 'INOUT' then 1
          end   

    , [parmDeclaration]
        =
              'declare '
            + p.[PARAMETER_NAME]
            + ' '
            + cteDT.datatype
            + cteDT.[maxLenghtInferred]

            COLLATE DATABASE_DEFAULT

    , [parmSend]
        =
            case p.[ORDINAL_POSITION]
                when 1 then ' '
                else ', '
            end
            + p.[PARAMETER_NAME]
            + ' = '
            + p.[PARAMETER_NAME]

            COLLATE DATABASE_DEFAULT

/*

    , [is_nullable]
        = null

    , [has_default_value]
        = null

    , [default_value]
        = null

 */

FROM [LS].[dbContext].INFORMATION_SCHEMA.ROUTINES sp

JOIN [LS].[dbContext].INFORMATION_SCHEMA.SCHEMATA tblSS
    ON  sp.SPECIFIC_CATALOG = tblSS.[CATALOG_NAME]
    AND sp.SPECIFIC_SCHEMA = tblSS.[SCHEMA_NAME]

JOIN [LS].[dbContext].INFORMATION_SCHEMA.PARAMETERS  p
    ON  sp.SPECIFIC_CATALOG = p.SPECIFIC_CATALOG
    AND sp.SPECIFIC_SCHEMA  = p.SPECIFIC_SCHEMA
    AND sp.SPECIFIC_NAME    = p.SPECIFIC_NAME

INNER JOIN cteDataType cteDT
    ON  p.SPECIFIC_CATALOG  = cteDT.[catalog]
    AND p.SPECIFIC_SCHEMA   = cteDT.[schema]
    AND p.SPECIFIC_NAME     = cteDT.[object]
    AND p.ORDINAL_POSITION  = cteDT.parameter_id

order by
          p.SPECIFIC_CATALOG
        , p.SPECIFIC_SCHEMA
        , p.SPECIFIC_NAME
        , p.ORDINAL_POSITION

References

  1. System Catalog Views
    • sys.parameters

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s