locked
Is it possible to create dynamically user defined table types RRS feed

  • General discussion

  • I have a table name and i need to create a user defined table type with the columns under the given table name(i will know only the table name )
    • Edited by selva173 Monday, March 23, 2015 12:22 PM
    Monday, March 23, 2015 10:31 AM

All replies

  • Hmm strange requirement but see if it helps you

    select  columns.* 
    from    information_schema.columns columns
    join    information_schema.tables  tables
    on      tables.table_name = columns.table_name
    where   tables.table_type = 'base table'
    where table_name=<your table>
    order by columns.table_name, columns.column_name


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, March 23, 2015 12:14 PM
    Answerer
  • For user defined table type such as

    create type typename as table

    (

    --- table columns must iterate here---

    )

    Is this possible?


    • Edited by selva173 Monday, March 23, 2015 2:12 PM
    Monday, March 23, 2015 2:12 PM
  • And how would you refer to such a thing in your code?  Given your question, I'll suggest that you start over.  Completely.  You appear to have chosen a path prematurely - one that is beyond your skills at this point.  So - what are you trying to achieve?  What is your goal?  Keep in mind that a table is intended to model a specific thing.  If you find that you need to dynamically define "things" within a relational database, then you have probably taken the wrong path to an efficient and effective solution. TSQL and relational databases do not lend themselves to patterns and techniques that you might use in a application development environment. 
    Monday, March 23, 2015 2:33 PM
  • So - what are you trying to achieve? 

    I'm going to guess that OP is trying to implement "master data management", a term that is apparently being misused all over town to build systems that automagically adjust to the arrival of new data values, fields, and relations.

    OP, technically it is possible to do whatever you want with dynamic SQL, but as Scott suggests this is somewhat at odds with what a relational database is all about.

    What *are* you trying to achieve?

    Josh

    Monday, March 23, 2015 5:31 PM
  • I have a table name and i need to create a user defined table type with the columns under the given table name(i will know only the table name )

    It is (as long as the table does not include features not supported by table-types), but I am not sure that I would recommend it. Like Scott, I would like to know the full story before I give any advice.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, March 23, 2015 10:38 PM
  • Hi,

    The story behind the scene is am trying to create a user defined table type dynamically . Am passing the table name to stored procedure which will create the user defined table type dynmaically for the columns under the table.

          Is that possible?

    ~Selva

    Tuesday, March 24, 2015 12:47 PM
  • Select top 0 * into yourNewTable from yourtemplateTable

    This will not work if you need other table structure like keys, indexes etc

    Tuesday, March 24, 2015 7:46 PM
  • Once again - what do you intend to use this user-defined table type for?  Creating a user-defined table type is done via the appropriate tsql statement - create type.  Can you do this in a stored procedure?  Yes.  Can you create it using a "name" you pass into the stored procedure?  Yes?  And then what? How will any of your existing code make use of it? 

    Tuesday, March 24, 2015 8:11 PM
  • The story behind the scene is am trying to create a user defined table type dynamically . Am passing the table name to stored procedure which will create the user defined table type dynmaically for the columns under the table.

    No, this is not the full story. You are repeating what you already have recited from page 80, but you never told us what it says on page 1-79, and how you ended up in this forsaken place.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, March 24, 2015 11:04 PM
  • So far I've understand form you that you need to know the table definition :

    Syntax :

    CREATE TABLE table_name
    (
    column_name1 data_type(size),
    column_name2 data_type(size),
    column_name3 data_type(size),
    ....
    );

    Example :

    CREATE TABLE Persons
    (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
    );

    Ref :


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Wednesday, March 25, 2015 6:37 AM