Post reply

How to reterive user defined Table type (Table value parmeter) in query analyser

  • December 29, 2010 at 12:13 am

    #232401

    Hi

    i have create one table in sql2008

    Below one is my sample one

    CREATE TYPE [dbo].[GetProgramsPrivileges] AS TABLE(

    [pg_id] [int] NULL,

    [pg_name] [varchar](50) NULL,

    [pg_actions] [varchar](9) NULL

    )

    how to view table structure in query analyser.

    Ihave tried like this sp_helptext [GetProgramsPrivileges]

    but i didnt get the structure. can you please guide me

  • December 29, 2010 at 12:48 am

    #1267045

    write

    sp_help <tablename>

    or

    sp_help GetProgramsPrivileges

  • December 29, 2010 at 1:19 am

    #1267050

    please give feadback if this help you or not

  • December 29, 2010 at 9:54 am

    #1267282

    You can also use INFORMATION_SCHEMA.COLUMNS.

    SELECT * FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = 'GetProgramsPrivileges'

    If there are certain results you like from sp_help you can take the code from that stored procedure and make your own script.

    -- get code from sp_help stored proc

    sp_helptext 'sp_help'

  • January 31, 2017 at 2:14 pm

    #1925962

    I know this is an old thread but since all the answers here are wrong I thought I'd drop what looks to work for me.

    Remember, we're looking for the definition for a table _TYPE_, not a table.


    SELECT *
    FROM sys.columns c
    JOIN sys.table_types tt ON c.[object_id] = tt.type_table_object_id
    WHERE tt.name = 'example_table_type'

    http://stackoverflow.com/questions/18584833/where-does-the-table-type-gets-stored-in-sql-server-and-how-can-we-alter-it

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply