Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2010
    Posts
    75

    Unanswered: get Stored Procedure Signature?

    Simple question. I googled it for 10 minutes but couldnt find an answer, but I'd be very surprised if you couldnt do this.

    I have a stored procedure that returns quite a lot of columns. I want a quick way to show the columns and data types without having to manually type them out.

    Similar to how you can right click on a table and say "Script to Query window" and get this:

    CREATE TABLE [dbo].[TBL_WHATEVER](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Artifact_Code] [nchar](3) NOT NULL,
    [Modifier_Name] [nvarchar](250) NOT NULL,
    [Sort_Order] [int] NULL,


    I would like to do something similar with a stored procedure. Can SQL server not detect the datatyps its going to return, and automate that for me?

    Thanks!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    That's about it. There is a hard definition of what will go in to a stored procedure, but just about anything can come out, especially when you start looking down the rat hole at TRY/CATCH, and IF statements.

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I have done this before.

    Can't find the code right now, but I can give you a similar utility that I wrote a long time ago to format SELECT statements in the format that I like to use.

    Since this is already looking at sys.columns (which has in it all of the type configurations: system_type_id, max_length, precision, scale) this can easily be modified with a giant case expression to do what you want.

    There is no reason this can't be done. Yeah, with every new edition of SQL you might have to update your case expression with the latest type definition, and it probably won't handle user types, but for 99.9999999999999999% of what you are going to need it for, it will work just fine.

    Here's something you can start with:

    Code:
    ALTER PROCEDURE [dbo].[ksp_PrintFields]
    
    @TabName sysname
    
    as
    
    /* TEST SECTION
    ksp_PrintFields 'tblTest' with recompile
    declare @TabName sysname='tblTest'
    --*/
    
    declare @ColumnCount int
    declare @OutputString varchar(max)
    
    set @ColumnCount=0
    set @OutputString='select   '
    
    while @ColumnCount<
    	(
    	select	Max(column_id)
    	from	sys.columns
    	where	object_id=
    				(
    				select	object_id
    				from	sys.objects
    				where	name=@TabName
    				)
    	)
    	begin
    	set @ColumnCount=@ColumnCount+1
    
    	set @OutputString=@OutputString+
    			(
    			select	name
    			from	sys.columns
    			where	object_id=
    						(
    						select	object_id
    						from	sys.objects
    						where	name=@TabName
    						)
    					and column_id=@ColumnCount
    			)+char(13)+char(10)+'        ,'
    
    	end
    
    set @OutputString=left(@OutputString,len(@OutputString)-9)+'from    dbo.'+@TabName+' t with (NOLOCK)'
    
    print @OutputString
    
    select * from sys.columns
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    MARS blows the idea of mapping output from a stored procedure out of the water. Structurally different result sets based on different parameter values is another problem that has plagued me for over a decade.

    While this is a great idea and might be workable based on a specific set of well behaved stored procedures, I know that there isn't a general solution for this problem... There are too many wrinkles in terms of odd code that blows you sky high for my taste.

    At least in the output files that I have handy, even SQL-Spec doesn't document the result sets for a stored procedure. It would be my best guess at a tool that might be able to generically extract the data for you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2010
    Posts
    75
    Thanks guys.
    I realized shortly after posting that I probably wasnt going to get an easy answer - you're right theres just too many possibilities of what could come out of a sproc for SQL Server to be able to tell me.

    But for any particular specific execution of the sproc, the data returned is a specific data table, not some mysterious cloud of possibilities. And SQL Server knows the names and datatypes of that data table. Its too bad I cant extract that info in text form somewhere (for a specific execution)

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by BeerOclock View Post
    But for any particular specific execution of the sproc, the data returned is a specific data table, not some mysterious cloud of possibilities.
    Even that is not true. I could easily write a stored procedure that returns different result sets for the same parameters at different times of the day. There might even be a business case for such a monstrosity.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2010
    Posts
    75
    blindman, thats true, but I was talking about a specific execution of the sproc. Not a specific set of parameters passed to it.

    So, say you execute your crazy sproc on 9:37am on a Tuesday in July with a full moon. Regardless of how it decides what to return, it returns something. Whatever data table it returns, with however meany columns with whatever data types, I'd like to be able to grab that in this text format:

    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Artifact_Code] [nchar](3) NOT NULL,
    [Modifier_Name] [nvarchar](250) NOT NULL,
    [Sort_Order] [int] NULL,

    Ive given up on this question though. It seems like its just too much to ask from SQL Server for some reason, so I bit the bullet and just typed it out manually
    Would still be nice to know if anyone ever comes up with an answer.

    Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •