Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Unanswered: Trying to complete this system query

    Code:
     
    
    SELECT  schemas.name AS SchemaName
    ,all_objects.name AS TableName 		
    --,syscolumns.id AS ColumnId		
    ,syscolumns.name  AS ColumnName
    ,syscolumns.colid AS ColumnID 
    ,syscomments.TEXT AS ColumnDefault
    ,sys.all_objects.type
    ,sys.all_objects.type_desc
    ,syscolumns.isnullable AS IsNullable 		
    ,systypes.name AS DataType
    ,syscolumns.length AS CharacterMaximumLength
    ,sysproperties.[value] AS Table_Description  ---- BUT TABLE DESCRIPTION HERE
    ,sysproperties.[value] AS Column_Description  ---- Column Description
    --,sysindex.name AS [IndexName]   ---Just added join (***Doesn't work pulls back duplicates for each column, removed views)
    				FROM syscolumns 		
    					INNER JOIN sys.systypes  
    						ON syscolumns.xtype = systypes.xtype 		
    					LEFT JOIN sys.all_objects  
    						ON syscolumns.id = all_objects.[object_id]		
    					LEFT OUTER JOIN sys.extended_properties AS sysproperties 
    						ON (sysproperties.minor_id = syscolumns.colid AND sysproperties.major_id = syscolumns.id) 		
    					LEFT OUTER JOIN sys.syscomments  
    						ON syscolumns.cdefault = syscomments.id 		
    				    LEFT OUTER JOIN sys.schemas 
    						ON schemas.[schema_id] = all_objects.[schema_id]
    					--JOIN sys.index_columns as systemindexcolumns 
    					--	ON systemindexcolumns.object_id = all_objects.object_id  ---Just added join these can be removed
    					--JOIN sys.indexes as sysindex
    					--	ON sysindex.object_id = systemindexcolumns.object_id  ---Just added join these can be removed
    								WHERE syscolumns.id IN (SELECT id                            
    									  FROM   sysobjects                           
    									       WHERE  xtype = 'U' or xtype = 'v')          
                                                    AND (systypes.name <> 'sysname')
                                                        AND (sys.all_objects.is_ms_shipped = '0')
    ORDER BY type_desc, TableName, syscolumns.colid
                                                ]



    Code:
    SELECT [Table_Name] = t.name, [Table_Description] = sp.value
    FROM sys.tables as t
    INNER JOIN sys.extended_properties as sp
    ON sp.major_id = t.object_id
    WHERE minor_id = '0'   ------Find Table Notes
    Order By Table_Name
    I am trying to get a column in the select from table description . It's been a while since I messed with system tables but I am trying to somehow only pull back table info, I believe the ordinal value for table is 0 and 1 - whatever is the column in the table.

    Is there a strategy I could leverage to get the table to only pull 0 descriptions, (NULL IS FINE) if nothing is there and then sequence the following column (COLUMNS DESCRIPTIONS) 1 through whatever.

    Thoughts? I really do appreciate it.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sorry dude, your question isn't really clear to me!
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Tables and Columns have a system property [value] which displays the results of the descriptions manually placed in MS SQL under properties, both table and column. This query get's to that information however table always is listed as 1 and any columns that have properties assume a ordinal value in the sequence of the columns are displayed in the table.

    I was trying to capture table properties in it's own column and columns descriptions in another. Null or repeating values in the Tables column is not a problem.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Something like this?
    Code:
    SELECT all_objects.name As object_name
         , table_properties.value As table_description
         , column_properties.value As column_description
    FROM   sys.all_objects
     INNER
      JOIN sys.columns
        ON columns.object_id = all_objects.object_id
     LEFT
      JOIN sys.extended_properties As table_properties
        ON table_properties.major_id = all_objects.object_id
       AND table_properties.minor_id = 0
     LEFT
      JOIN sys.extended_properties As column_properties
        ON column_properties.major_id = columns.object_id
       AND column_properties.minor_id = columns.column_id
    ;
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Thanks your code inspired me to move into a different direction. I was in CTE UNION land for some bizarre reason.

    Either you are really good at this stuff or you aren't. I'm "ok" at best lol.

    I'll post my code when I am done.

  6. #6
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Code:
    SELECT  schemas.name AS SchemaName
    ,all_objects.name AS TableName 		
    --,syscolumns.id AS ColumnId		
    ,syscolumns.name  AS ColumnName
    ,syscolumns.colid AS ColumnID 
    ,syscomments.TEXT AS ColumnDefault
    ,sys.all_objects.type AS [Type]
    ,sys.all_objects.type_desc As [Type_Desc]
    ,syscolumns.isnullable AS IsNullable 		
    ,systypes.name AS DataType
    ,syscolumns.length AS CharacterMaximumLength
    ,Table_Properties.[value] AS [Table_Description]
    ,Column_Properties.[value] AS [Column_Description]  ---- Column Description
              FROM syscolumns 		
    					INNER JOIN sys.systypes  
    						ON syscolumns.xtype = systypes.xtype 		
    					LEFT JOIN sys.all_objects  
    						ON syscolumns.id = all_objects.[object_id]		
    					LEFT OUTER JOIN sys.extended_properties AS Column_Properties 
    						ON (Column_Properties .minor_id = syscolumns.colid AND Column_Properties.major_id = syscolumns.id)
    					LEFT OUTER JOIN sys.extended_properties AS Table_Properties 
    						ON (Table_Properties .minor_id = 0 AND Table_Properties.major_id = syscolumns.id)
    					LEFT OUTER JOIN sys.syscomments  
    						ON syscolumns.cdefault = syscomments.id 		
    				    LEFT OUTER JOIN sys.schemas 
    						ON schemas.[schema_id] = all_objects.[schema_id]
    			
    								WHERE syscolumns.id IN (SELECT id                            
    									  FROM   sysobjects                           
    									       WHERE  xtype = 'U' or xtype = 'v')          
                                                    AND (systypes.name <> 'sysname')
                                                        AND (sys.all_objects.is_ms_shipped = '0')
    ORDER BY type_desc, TableName, syscolumns.colid
    For the life of me I was just treading water trying to union for some bizarre reason.

    Anyway Thanks George for the follow up it helped a lot. It gave me confidence and reassurance.

    Now the query will pull back tables descriptions and columns. If you have more then one comment for an object or column it will pull back duplicate rows.

    Maybe someone else will find this helpful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •