Data Dictionary

The following script is used to create Data Dictionary for all tables.

SELECT ST.NAME 'TABLE'
	,SC.NAME 'COLUMN'
	,T.NAME 'COLUMN TYPE'
	,SC.MAX_LENGTH 'LENGTH'
	,EX.VALUE 'DESCRIPTION'
FROM SYS.TABLES ST
INNER JOIN SYS.COLUMNS SC ON ST.OBJECT_ID = SC.OBJECT_ID
INNER JOIN SYS.TYPES T ON SC.SYSTEM_TYPE_ID = T.SYSTEM_TYPE_ID
	AND SC.SYSTEM_TYPE_ID = T.USER_TYPE_ID
LEFT JOIN SYS.EXTENDED_PROPERTIES EX ON EX.MINOR_ID = SC.COLUMN_ID
	AND EX.MAJOR_ID = ST.OBJECT_ID
 

However these above script can be modified to get details of any specific schema.

SELECT ST.NAME 'TABLE'
	,SC.NAME 'COLUMN'
	,T.NAME 'COLUMN TYPE'
	,SC.MAX_LENGTH 'LENGTH'
	,EX.VALUE 'DESCRIPTION'
FROM SYS.TABLES ST
INNER JOIN sys.schemas SS ON SS.schema_id = st.schema_id
INNER JOIN SYS.COLUMNS SC ON ST.OBJECT_ID = SC.OBJECT_ID
INNER JOIN SYS.TYPES T ON SC.SYSTEM_TYPE_ID = T.SYSTEM_TYPE_ID
	AND SC.SYSTEM_TYPE_ID = T.USER_TYPE_ID
LEFT JOIN SYS.EXTENDED_PROPERTIES EX ON EX.MINOR_ID = SC.COLUMN_ID
	AND EX.MAJOR_ID = ST.OBJECT_ID
WHERE SS.NAME = 'Guest'

Thanks

Manish