SQL Server Performance

How to get the table type definition

Discussion in 'SQL Server 2008 General DBA Questions' started by AJITH123, Jul 29, 2010.

  1. AJITH123 Member

    Hi,
    Can any body give the script to get the table type definition (sql script). What are the sys objects needs to joing to get the definition?
    Thanks
    Ajith
    AJITH123, Jul 29, 2010
    #2

  2. ashish287 New Member

    there are 2 SP which is very helpful.
    sp_help 'tablename' will give all the information about object you mentioned in code.
    sp_helptext will help you to read the code of the object like view/SP/fn
    hope it helps
    ashish287, Jul 29, 2010
    #3

  3. AJITH123 Member

    sp_help table name will give the definition not the script. I need the script like below
    CREATE
    TYPE [dbo].[Test] AS TABLE
    AJITH123, Jul 29, 2010
    #4

  4. satya Moderator

    You could try using OBJECT_DEFINITION function to obtain, see BOL:
    USE AdventureWorks2008R2;GOSELECT OBJECT_DEFINITION (OBJECT_ID(N'sys.sp_columns')) AS [Object Definition];GO
    HTH
    satya, Aug 2, 2010
    #5

  5. AJITH123 Member

    OK, but if I need to get the user defined table type definition (script), where should i query? The one which you mentioned will give the details of FN,SP , constraint etc.
    AJITH123, Aug 2, 2010
    #6

  6. satya Moderator

    Can you enlighten me what you mean by table type definition?> I think there is a bit of mis-interpretation of what you are looking and what we are giving you.
    satya, Aug 3, 2010
    #7

  7. AJITH123 Member

    Soory for the confusion!
    See the below example, I have created the below table type, now I got the below script from the enterprice manager [generate the script from the mentioned type]. I want to get the script through TSQL command.
    CREATE TYPE [dbo].[TableType] AS TABLE(
    [AccountType] [char](2) NULL,
    [UnitID] [int] NULL,
    [ParcelID] [bigint] NULL,
    [AccountBalance] [dbo].[CURRENCY_AMOUNT] NULL,
    [UnitBalance] [dbo].[UNIT_AMOUNT] NULL
    )
    GO
    AJITH123, Aug 4, 2010
    #8

  8. satya Moderator

    Hmm another conflict here [:)], you say Enterprise Manager and posted in SQL 2008 forums - so is it SQL 2000 or 2005 or 2008?
    satya, Aug 5, 2010
    #9

  9. AJITH123 Member

    [:$]
    It is in SQL server 2008, SQL server management studio (ssms) ..not enterprise manager [:(]
    AJITH123, Aug 5, 2010
    #10

  10. ashish287 New Member

    as I suggested you to run sp_helptext for objects, and if you run it againt tables then you will get the error as :-There is no text for object 'databaseactions'
    which is self explanatory
    ashish287, Aug 12, 2010
    #11

  11. Luis Martin Moderator

    1) Run Profiler.
    2) With SSMS get the script (like you did).
    3) Check TextData column in Profiler.
    Just a thought.
    Luis Martin, Aug 12, 2010
    #12

  12. satya Moderator

    Ok it is getting clear now, you can only query against the system catalog sys.table_types to get further information on type that is created.
    [quote user="AJITH123"]
    Soory for the confusion!
    See the below example, I have created the below table type, now I got the below script from the enterprice manager [generate the script from the mentioned type]. I want to get the script through TSQL command.
    CREATE TYPE [dbo].[TableType] AS TABLE(
    [AccountType] [char](2) NULL,
    [UnitID] [int] NULL,
    [ParcelID] [bigint] NULL,
    [AccountBalance] [dbo].[CURRENCY_AMOUNT] NULL,
    [UnitBalance] [dbo].[UNIT_AMOUNT] NULL
    )
    GO
    [/quote]
    satya, Aug 18, 2010
    #13

Share This Page