SQL Query Table - Description of Column(edit)

https://stackoverflow.com/questions/15161505/use-a-query-to-access-column-description-in-sql

https://devio.wordpress.com/2009/08/19/retrieving-table-and-column-descriptions-in-sql-server/

https://dataedo.com/kb/tools/ssms/how-to-view-and-edit-table-and-column-comments

https://www.mssqltips.com/sqlservertutorial/183/informationschemacolumns/

---------- 1.To retrieve the descriptions of all columns: ----------

select 
        st.name [Table],
        sc.name [Column],
        sep.value [Description]
    from sys.tables st
    inner join sys.columns sc on st.object_id = sc.object_id
    left join sys.extended_properties sep on st.object_id = sep.major_id
                                         and sc.column_id = sep.minor_id
                                         and sep.name = 'MS_Description'
    --where st.name = @TableName
    --and sc.name = @ColumnName


---------- 2.To retrieve the descriptions of all tables: ----------

SELECT sys.objects.name AS TableName, ep.name AS PropertyName,
       ep.value AS Description
FROM sys.objects
CROSS APPLY fn_listextendedproperty(default,
                                    'SCHEMA', schema_name(schema_id),
                                    'TABLE', name, null, null) ep
WHERE sys.objects.name NOT IN ('sysdiagrams')
ORDER BY sys.objects.name

---------- 3.To retrieve the descriptions of all column that is nullable: ----------
;WITH src
AS (
    SELECT sys.objects.name                AS TableName,
           sys.columns.name                AS ColumnName,
           CAST(ep.value AS NVARCHAR(255)) AS Description,           
		   sys.columns.is_nullable         AS IsNullable,
		   sys.columns.default_object_id   AS DefaultValue,
		   ep.name                         AS PropertyName
    FROM sys.objects
        INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id
        CROSS APPLY fn_listextendedproperty(
                                               DEFAULT,
                                               'SCHEMA',
                                               SCHEMA_NAME(schema_id),
                                               'TABLE',
                                               sys.objects.name,
                                               'COLUMN',
                                               sys.columns.name
                                           ) ep
     WHERE sys.objects.name = 'SupportDevice' 
    -- AND sys.columns.name = 'StatusDefault'	
	 AND sys.columns.is_nullable = 1
	-- AND sys.columns.is_nullable = 0
)
select * from src order by TableName,ColumnName 
--select * from src order by TableName,ColumnName 

---------- 4.To retrieve the descriptions of all table columns: ----------

SELECT sys.objects.name AS TableName, sys.columns.name AS ColumnName,
       ep.name AS PropertyName, ep.value AS Description
FROM sys.objects
INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id
CROSS APPLY fn_listextendedproperty(default,
                  'SCHEMA', schema_name(schema_id),
                  'TABLE', sys.objects.name, 'COLUMN', sys.columns.name) ep
--WHERE sys.objects.object_id = object_id('SupportDevice')
ORDER BY sys.objects.name, sys.columns.column_id


---------- 5.To retrieve the name of all columns in a table: ----------

select 
 coalesce(cast(exprop.objname as varchar) collate SQL_Latin1_General_CP1_CI_AS,
  syscol.name) as colname, 
 coalesce(cast(exprop.value as varchar) collate SQL_Latin1_General_CP1_CI_AS,
  syscol.name) as colalias
from sys.columns syscol 
 left outer join fn_listextendedproperty(
   'caption', 
   N'schema', 'dbo', 
   N'table', 'person', 
   'column', default
  ) exprop on
  cast(exprop.objname as varchar) collate SQL_Latin1_General_CP1_CI_AS = 
  cast(syscol.name as varchar) collate SQL_Latin1_General_CP1_CI_AS 
where syscol.object_id = object_id('SupportDevice')