Select column names and top 1 records along.

  • keneangbu

    Mr or Mrs. 500

    Points: 566

    This query retrieves column names and their data type along with the tables they're in.

    SELECT

    st.name 'Table Name',

    c.name 'Column Name',

    t.name 'Data Type'

    FROM sys.columns c

    INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

    LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id

    LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id

    LEFT OUTER JOIN sys.tables st ON st.object_id = i.object_id

    I was trying to select top 1 column value from the table to have a glimpse of the data, based on the output

    (i.e. equivalently,

    SELECT c.name FROM st.Name

    I have been trying to use a dynamic sql, but as it should put the table name in a single quotation as a string, it couldn't work; when I try to avoid that, it just displays the declared variable.

    Any idea is much appreciated. Thanks!

    • This topic was modified 4 months, 3 weeks ago by  keneangbu.
    • This topic was modified 4 months, 3 weeks ago by  keneangbu.
  • Joe Torre

    SSChampion

    Points: 10238

    SELECT (TOP 1)

    st.name ‘Table Name’,

    c.name ‘Column Name’,

    t.name ‘Data Type’

    FROM sys.columns c

    INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

    LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id

    LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id

    LEFT OUTER JOIN sys.tables st ON st.object_id = i.object_id

  • ScottPletcher

    SSC Guru

    Points: 98053

    You don't want index_columns and indexes in the query as it exists, because it will just generate duplicate rows for no reason.

    SELECT TOP (1)

    st.name [Table Name],

    c.name [Column Name],

    t.name [Data Type]

    FROM sys.columns c

    INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

    LEFT OUTER JOIN sys.tables st ON st.object_id = i.object_id

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

Viewing 3 posts - 1 through 3 (of 3 total)

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