USE [AdventureWorks];
SET NOCOUNT ON

DECLARE @tableName AS NVARCHAR(128)

PRINT 'Database ' + db_name()
PRINT 'Document created at ' + Convert(char(10), getdate(), 103)
PRINT ''

DECLARE tables_cursor CURSOR FOR SELECT [name] FROM sys.all_objects WHERE type_desc = 'USER_TABLE'
OPEN tables_cursor

FETCH NEXT FROM tables_cursor INTO @tableName
WHILE @@FETCH_STATUS = 0 BEGIN
   
   PRINT 'Table: ' + @tableName
   
   SELECT DISTINCT c.name AS [Column],ISC.data_type AS [Type],c.max_length AS [Length], CASE ISC.is_nullable WHEN 'NO' THEN 'YES' ELSE 'NO' END AS [IsNullable],
   isnull(ISC.column_default,'') AS [Default value], CASE c.is_identity WHEN '0' THEN '' ELSE 'YES' END AS [Auto increment], ISNULL(CASE WHEN i.is_primary_key = 1 THEN 'Primary key '
   WHEN i.is_unique = 1 THEN 'Unique key ' ELSE 'Key with duplicates ' END + 'Type '+ i.type_desc,'') AS [Key],
   CASE WHEN isnull(fc.name,'0') = '0' THEN '' ELSE 'Foreign key : ' + isnull(allObj.name,'') + '.' + isnull(allCols.name,'') END AS [Constraint],
   isnull(ep.value,'') as description
   FROM sys.all_objects o 
   INNER JOIN  sys.all_columns c ON o.object_id = c.object_id 
   LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS ISC ON ISC.table_name = o.name AND  c.name = ISC.column_name 
   LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON ccu.table_name = o.name AND  c.name = ccu.column_name 
   LEFT OUTER JOIN sys.foreign_keys fc on fc.name = ccu.constraint_name 
   LEFT OUTER JOIN sys.all_columns allCols ON allCols.object_id = fc.referenced_object_id AND fc.key_index_id = allCols.column_id
   LEFT OUTER JOIN sys.all_objects allObj ON allObj.object_id = allCols.object_id 
   LEFT OUTER JOIN sys.index_columns idxCols ON o.object_id = idxCols.object_id AND idxCols.column_id = c.column_id
   LEFT OUTER JOIN sys.indexes i ON i.object_id = idxCols.object_id AND idxCols.index_id = i .index_id 
 left outer join (select * from sys.extended_properties where name = 'MS_Description') ep on o.object_id = ep.major_id and c.column_id = ep.minor_id
   WHERE o.name =  @tableName
   
   FETCH NEXT FROM tables_cursor INTO @tableName
END

CLOSE tables_cursor
DEALLOCATE tables_cursor