WITH primaryKeys AS
(
      SELECT  
                  INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME,
                  COLUMN_NAME,
                  INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
      FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON 
                        information_schema.KEY_COLUMN_USAGE.TABLE_NAME = information_schema.TABLE_CONSTRAINTS.TABLE_NAME AND
                        information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME = information_schema.TABLE_CONSTRAINTS.CONSTRAINT_NAME
      WHERE   CONSTRAINT_TYPE = 'PRIMARY KEY'
), foreignKeys AS
(
      SELECT  
                  INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME,
                  COLUMN_NAME,
                  INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
      FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON 
                        information_schema.KEY_COLUMN_USAGE.TABLE_NAME = information_schema.TABLE_CONSTRAINTS.TABLE_NAME AND
                        information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME = information_schema.TABLE_CONSTRAINTS.CONSTRAINT_NAME
      WHERE   CONSTRAINT_TYPE = 'FOREIGN KEY'
), uniqueKeys AS
(
      SELECT  
                  INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME,
                  COLUMN_NAME,
                  INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
      FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON 
                        information_schema.KEY_COLUMN_USAGE.TABLE_NAME = information_schema.TABLE_CONSTRAINTS.TABLE_NAME AND
                        information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME = information_schema.TABLE_CONSTRAINTS.CONSTRAINT_NAME
      WHERE   CONSTRAINT_TYPE = 'UNIQUE'
)
SELECT
      INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION,
      INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA, 
      INFORMATION_SCHEMA.COLUMNS.TABLE_NAME, 
      INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
      information_schema.COLUMNS.DATA_TYPE
          + ' ' +
      case 
            when information_schema.COLUMNS.DATA_TYPE IN (N'numeric', N'decimal') then 
                  '(' + cast( information_schema.COLUMNS.NUMERIC_PRECISION as varchar ) + ', ' + cast( information_schema.COLUMNS.NUMERIC_SCALE as varchar ) + ') '
            when information_schema.COLUMNS.DATA_TYPE IN (N'datetime2', N'datetimeoffset', N'time') then  
                  '(' + cast( information_schema.COLUMNS.DATETIME_PRECISION as varchar ) + ') '
            when information_schema.COLUMNS.DATA_TYPE IN (N'geography', N'geometry', N'hierarchyid', N'image', N'ntext', N'sql_variant', N'text', N'xml') then  
                  ''
            else     
                  case when information_schema.COLUMNS.CHARACTER_MAXIMUM_LENGTH is not null then REPLACE('('+ CAST( information_schema.COLUMNS.CHARACTER_MAXIMUM_LENGTH as varchar)  +')', '(-1)', '(max)') else '' end 
      end as DATA_TYPE,
      
      COALESCE(information_schema.COLUMNS.COLUMN_DEFAULT,' ') as  DEFAULT_VALUE,
        COALESCE(sys.extended_properties.value,' ') AS COLUMN_DESCRIPTION,

      COALESCE(primaryKeys.CONSTRAINT_NAME,' ') as PRIMARY_KEY,
      COALESCE(foreignKeys.CONSTRAINT_NAME,' ') as FOREIGN_KEY,
      COALESCE(uniqueKeys.CONSTRAINT_NAME,' ') as UNIQUE_KEY,

      COALESCE(OBJECT_NAME(sys.foreign_key_columns.referenced_object_id ) + '.' + referencedColumn.COLUMN_NAME,' ') AS FK_REFERENCE
      
      into #TableInformation
      
      FROM INFORMATION_SCHEMA.COLUMNS
            LEFT OUTER JOIN primaryKeys ON
                  INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = primaryKeys.TABLE_NAME AND 
                  INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = primaryKeys.COLUMN_NAME
            LEFT OUTER JOIN foreignKeys ON
                  INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = foreignKeys.TABLE_NAME AND 
                  INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = foreignKeys.COLUMN_NAME
            LEFT OUTER JOIN uniqueKeys ON
                  INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = uniqueKeys.TABLE_NAME AND 
                  INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = uniqueKeys.COLUMN_NAME
            LEFT OUTER JOIN sys.foreign_key_columns ON
                          INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = OBJECT_NAME(sys.foreign_key_columns.parent_object_id) AND
                          INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION = sys.foreign_key_columns.parent_column_id
                LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS referencedColumn ON
                          OBJECT_NAME(sys.foreign_key_columns.referenced_object_id) = referencedColumn.TABLE_NAME AND
                          sys.foreign_key_columns.referenced_column_id = referencedColumn.ORDINAL_POSITION
                LEFT OUTER JOIN sys.extended_properties ON
                          OBJECT_ID(INFORMATION_SCHEMA.COLUMNS.TABLE_NAME) = sys.extended_properties.major_id AND
                          INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION = sys.extended_properties.minor_id AND
                          sys.extended_properties.class = 1 AND
                          sys.extended_properties.name = 'MS_Description'
      where  INFORMATION_SCHEMA.COLUMNS.table_schema = 'dbo' 
      ORDER BY information_schema.COLUMNS.TABLE_NAME,  information_schema.COLUMNS.ORDINAL_POSITION




declare @Schema nvarchar(max) = 'dbo'
declare @tableName nvarchar(max) = ''
declare @tableDescription nvarchar(max) = ''
declare @HTML nvarchar(max) = ''

declare cursorTables CURSOR FOR  
	select
		TABLE_NAME, 
		cast( value as nvarchar(max) ) as TABLE_DESCRIPTION
	from sys.extended_properties 
		inner join INFORMATION_SCHEMA.TABLES on 
			OBJECT_NAME(sys.extended_properties.major_id) = INFORMATION_SCHEMA.TABLES.TABLE_NAME and
			sys.extended_properties.minor_id = 0 and
			sys.extended_properties.name = 'MS_Description' and
			INFORMATION_SCHEMA.TABLES.TABLE_TYPE = 'BASE TABLE' and
			INFORMATION_SCHEMA.TABLES.TABLE_NAME != 'sysdiagrams' and
			INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = @Schema
 
 
 
open cursorTables   
fetch next from cursorTables into @tableName, @tableDescription   

while @@fetch_status = 0   
begin   
    print '<table border="1">'
    print '<tr>'
        print '<td>'
            print '<b>Table name :- </b>' + @tableName + '<br />'
            print '<b>Description:- </b>' + @tableDescription
        print '</td>'
    print '</tr>'
    
    print '</table>'
    

    
    print '<table border="1">
    <tr>
        <td>COLUMN_NAME</td><td>&nbsp;</td>
        <td>COLUMN_DESCRIPTION</td><td>&nbsp;</td>
        <td>DATA_TYPE</td><td>&nbsp;</td>
        <td>DEFAULT_VALUE</td><td>&nbsp;</td>
        <td>PRIMARY_KEY</td><td>&nbsp;</td>
        <td>UNIQUE_KEY</td><td>&nbsp;</td>
        <td>FOREIGN_KEY</td><td>&nbsp;</td>
        <td>FK_REFERENCE</td><td>&nbsp;</td>
    </tr>'

    set @HTML = ''
    
    declare cursorTableInformation CURSOR FOR  
        select '<tr>
        <td>'+ COLUMN_NAME +'</td><td>&nbsp;</td>
        <td>'+ convert( nvarchar(max), COLUMN_DESCRIPTION   ) +'</td><td>&nbsp;</td>
        <td>'+ DATA_TYPE +'</td><td>&nbsp;</td>
        <td>'+ DEFAULT_VALUE +'</td><td>&nbsp;</td>
        <td>'+ PRIMARY_KEY +'</td><td>&nbsp;</td>
        <td>'+ UNIQUE_KEY +'</td><td>&nbsp;</td>
        <td>'+ FOREIGN_KEY +'</td><td>&nbsp;</td>
        <td>'+ FK_REFERENCE +'</td><td>&nbsp;</td>
    </tr>'
    from #TableInformation
    where TABLE_NAME = @tableName 
    
	open cursorTableInformation   
	fetch next from cursorTableInformation into @HTML

	while @@fetch_status = 0   
	begin   
    
	    print @HTML
	
		fetch next from cursorTableInformation into @HTML
	end

	CLOSE cursorTableInformation   
	DEALLOCATE cursorTableInformation    
    
    
    
    print '</table>'


    print '<hr/>'
    print 'Report date :- ' + convert( varchar(max), getdate() ) 
    print '<hr/>'

    fetch next from cursorTables into @tableName, @tableDescription  
end

CLOSE cursorTables   
DEALLOCATE cursorTables

drop table #TableInformation