Post reply

Table stucture in a database

  • November 16, 2012 at 9:57 am

    #270315

    Can some one please tell me any query to see the table structure(column name, type) in the database in sql server

  • November 16, 2012 at 10:08 am

    #1559869

    This work:

    http://www.sqlservercentral.com/scripts/scripting/69282/

  • November 16, 2012 at 12:50 pm

    #1559922

    No offense but that link is horrible. The script there is strangely complex and doesn't actually return correct results. It is not too far off but it is wrong. The length is incorrect when using nchar, nvarchar. Of course length for string type data is when it is most important to know the length.

    Consider the following:

    CREATE TABLE [dbo].[abc_test](

    [num_key] [int] NULL,

    [name] [nvarchar](20) NULL,

    [abc_testID] [int] IDENTITY(1,1) NOT NULL

    ) ON [PRIMARY]

    go

    exec xsp_Structure 'abc_test'

    That will tell you that the column [name] is nvarchar with a length of 40.

    To discuss the actual script there I don't begin to understand why the author felt the need to use a global temp table and dynamic sql. It would be far easier to simply get the data directly and skip the rest of it.

    declare @Table varchar(10) = 'abc_test'

    select ColName=[Name]

    ,ColType = type_name(user_type_id)

    ,ColLen= convert(int, case when user_type_id in (52,56,60,62,106) then precision else max_length end)

    ,ColDeci= case when user_type_id in (52,56,60,62,106) then scale else 0 end

    from sys.columns where object_id = object_id(@Table);

    The above will produce the same results but it is far simpler. If you take it one step further to handle the n(var)char it would look like this.

    select ColName=[Name]

    ,ColType = type_name(user_type_id)

    ,ColLen= convert(int, case when user_type_id in (52,56,60,62,106) then precision when user_type_id in (239, 231) then max_length / 2 else max_length end)

    ,ColDeci= case when user_type_id in (52,56,60,62,106) then scale else 0 end

    ,user_type_id

    from sys.columns where object_id = object_id(@Table);

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • November 17, 2012 at 5:58 am

    #1559996

    sp_columns <tablename>

  • November 17, 2012 at 2:04 pm

    #1560056

    Sean Lange (11/16/2012)

    No offense but that link is horrible.

    Lordy, yes. I'm not sure that it even lives up to the 2 starss it got.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 17, 2012 at 2:28 pm

    #1560058

    KcV (11/17/2012)

    sp_columns <tablename>

    Well done. Ever since they took away the {f4} key that was so very handy in the old Query Analyzer, I've fallen in love with sp_columns especially because of its ability to do wildcard searches.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 17, 2012 at 4:45 pm

    #1560068

    That's what I get for replying in a hurry. I grabbed the first script off the site without testing.

    My apologies.

  • November 17, 2012 at 5:26 pm

    #1560072

    Steve Jones - SSC Editor (11/17/2012)

    That's what I get for replying in a hurry. I grabbed the first script off the site without testing.

    My apologies.

    Heh... yeah... none of us have ever done such a thing before. 😛 I went for years thinking that a splitter that used concatenated delimiters was the berries.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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