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
  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
  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
  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
  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.
  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.
  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
  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?
  9. AJITH123 Member

    [:$]
    It is in SQL server 2008, SQL server management studio (ssms) ..not enterprise manager [:(]
  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
  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.
  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]
  13. TheMayneLine New Member

    I know it is a few years since this was posted. But I was lookign for the answer and this was not it :) I found the answer so here you go:

    I have a table type of InputCodeDestinationRateM1. This found the columns associated with that table type

    select * from sys.columns where object_id in
    (select type_table_object_id from sys.table_types where [name ] = 'InputCodeDestinationRateM1')

Share This Page