none
Join the 2 sys Queries

    Question

  • Hi All,

    Kindly help me in joining the below queries

    select c.name, t.name, c.xprec, c.xscale, c.length from syscolumns c
    join systypes t on c.xtype = t.xtype
    where ID in (
    select major_id from sys.extended_properties)

    and

    select object_name([major_id]) as [Table Name] , [name] as [Extended Properties], [Value]
    from sys.extended_properties
    group by object_name([major_id]),[name],[value]

    The Select should contain all the objects selected in both the select queries .

    Thanks in Advance

    Monday, July 08, 2013 5:35 PM

Answers

  • select c.name, t.name, c.xprec, c.xscale, c.length
    , 
    object_name([major_id]) as [Table Name] , ep.[name] as [Extended Properties], ep.[Value]
     from syscolumns c
    join systypes t on c.xtype = t.xtype
    inner join 
    sys.extended_properties ep
    on ep.major_id = c.id
    group by object_name([major_id]),ep.[name],ep.[value],
    c.name, t.name, c.xprec, c.xscale, c.length


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Priya Bange Tuesday, July 09, 2013 5:37 PM
    Monday, July 08, 2013 6:57 PM

All replies

  • Interesting.  First of all, what do you want to do with this query?  Why are you joining them together? Can you summarize what each of the subqueries returns?  How does the first query relate to the second query?  I am pretty sure that I can figure this out, but then I would deprive you of all of the fun.


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, July 08, 2013 5:44 PM
  • Ya thanks a ton  :D I have extended properties enabled on Tables so with query 2 i can get the description of the extended properties but

    with the help of query 1 am getting the column detailed information .

    SO i want a query via which i can have both extended properties , Name of the tables as well its value + column info which can be achieved by joining the above two queries .

    Thanks

    Monday, July 08, 2013 5:50 PM
  • select c.name, t.name, c.xprec, c.xscale, c.length
    , 
    object_name([major_id]) as [Table Name] , ep.[name] as [Extended Properties], ep.[Value]
     from syscolumns c
    join systypes t on c.xtype = t.xtype
    inner join 
    sys.extended_properties ep
    on ep.major_id = c.id
    group by object_name([major_id]),ep.[name],ep.[value],
    c.name, t.name, c.xprec, c.xscale, c.length


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Priya Bange Tuesday, July 09, 2013 5:37 PM
    Monday, July 08, 2013 6:57 PM
  • Convert

    where ID in (
    select major_id from sys.extended_properties)

    to

    join sys.extended_properties ON ID = major_id

    in the first part and then add a join to the second part as a table expression ( I am using a subquery here).

    join (

    select object_name([major_id]) as [Table Name] , [name] as [Extended Properties], [Value]
    from sys.extended_properties
    group by object_name([major_id]),[name],[value]

    ) AS Q2 ON Q2.major_id = extended_properties.major_id

    to get

    select c.name, t.name, c.xprec, c.xscale, c.length, q2.* from syscolumns c
    join systypes t on c.xtype = t.xtype
    join sys.extended_properties ON ID = major_id

    join (

    select object_name([major_id]) as [Table Name] , [name] as [Extended Properties], [Value]
    from sys.extended_properties
    group by object_name([major_id]),[name],[value]

    ) AS Q2 ON Q2.major_id = extended_properties.major_id


    If you're happy and you know it vote and mark.

    Monday, July 08, 2013 8:08 PM