SQLSERVER 表 数据字典导出

gistfile1.sql

--
--在设计表时将字段的Description加上
--然后执行下面的SQL就可以了
--
DECLARE @table_name NVARCHAR(1000)
SET @table_name = 'LuckDrawActivities'

SELECT   '' AS [Key], 
			sys.columns.name AS 'Field Name', 
			sys.types.name + '(' + CONVERT ( varchar(20) ,sys.columns.max_length ) + ')'   AS 'Data Type',
			(case
			sys.columns.is_nullable  when 0 then 'Y' else 'N' end) AS 'Required', 
			ISNULL((select definition FROM sys.default_constraints x WHERE x.object_id = sys.columns.default_object_id),'') AS 'DEFAULT',
			ISNULL((SELECT VALUE FROM   sys.extended_properties WHERE  sys.extended_properties.major_id = sys.columns.object_id AND sys.extended_properties.minor_id = sys.columns.column_id),'') AS Description ,
			'' AS Remark
FROM     sys.columns, 
         sys.tables, 
         sys.types 
WHERE    sys.columns.object_id = sys.tables.object_id 
         AND sys.columns.system_type_id = sys.types.system_type_id 
         AND sys.tables.name = @table_name 
         AND sys.types.name <> 'sysname' 
ORDER BY sys.columns.column_id